WikiAdmin: 1 revision imported

2023-07-14T12:58:50Z

1 revision imported

← Older revision

Revision as of 12:58, 14 July 2023

Chanson at 21:09, 16 December 2021

2021-12-16T21:09:33Z

New page

Interbase Databases have tables of data used to define the database structure. These tables all have rdb$ prefixes.

These examples demonstrate how to extract specific data from these tables.

Select all fields from all tables:
<PRE>
select rrf.rdb$field_name "Field Name",rdb$Relation_name,rdb$field_type "Field Type"
from rdb$relation_fields rrf
inner join rdb$fields rf on rf.rdb$field_name=rrf.rdb$field_source
where substr(rrf.rdb$field_name,1,4) not in ('RDB$','TMP$')
order by rrf.rdb$relation_name,rrf.rdb$field_position
</PRE>

Select all fields with "SESSION" as part of the name:
<PRE>
select rrf.rdb$field_name "Field Name",rdb$Relation_name "Table",rdb$field_type "Field Type"
from rdb$relation_fields rrf
inner join rdb$fields rf on rf.rdb$field_name=rrf.rdb$field_source
where substr(rrf.rdb$field_name,1,4) not in ('RDB$','TMP$')
and rrf.rdb$field_name like '%SESSION%'
order by rrf.rdb$relation_name,rrf.rdb$field_position
</PRE>

Select all date fields:
<PRE>
select rrf.rdb$field_name "Field Name",rdb$Relation_name "Table",rdb$field_type "Field Type"
from rdb$relation_fields rrf
inner join rdb$fields rf on rf.rdb$field_name=rrf.rdb$field_source
where substr(rrf.rdb$field_name,1,4) not in ('RDB$','TMP$')
AND rdb$field_type IN (12,35)
order by rrf.rdb$relation_name,rrf.rdb$field_position
</PRE>
NOTE: Field type 12 is date, and 35 is timestamp.

Example with Field Type Descriptions for a table:
<PRE>
SELECT
RF.RDB$FIELD_NAME FIELD_NAME,
CASE F.RDB$FIELD_TYPE
WHEN 7 THEN
CASE F.RDB$FIELD_SUB_TYPE
WHEN 0 THEN 'SMALLINT'
WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
WHEN 2 THEN 'DECIMAL'
END
WHEN 8 THEN
CASE F.RDB$FIELD_SUB_TYPE
WHEN 0 THEN 'INTEGER'
WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
WHEN 2 THEN 'DECIMAL'
END
WHEN 9 THEN 'QUAD'
WHEN 10 THEN 'FLOAT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 14 THEN 'CHAR(' || ((F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
WHEN 16 THEN
CASE F.RDB$FIELD_SUB_TYPE
WHEN 0 THEN 'BIGINT'
WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
WHEN 2 THEN 'DECIMAL'
END
WHEN 27 THEN 'DOUBLE'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR(' || ((F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
WHEN 40 THEN 'CSTRING' || ((F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
WHEN 45 THEN 'BLOB_ID'
WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
END FIELD_TYPE,COALESCE(RF.RDB$NULL_FLAG, 0) XX,
case COALESCE(RF.RDB$NULL_FLAG, 0) WHEN 0 then 'NULL' else 'NOT NULL' end FIELD_NULL,
CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
DCO.RDB$COLLATION_NAME FIELD_COLLATION,
COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
F.RDB$VALIDATION_SOURCE FIELD_CHECK,
RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (RF.RDB$RELATION_NAME = :TABLE_NAME) AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$FIELD_POSITION;
</pre>

Dependencies (Foreign Keys) with fields:
<pre>
SELECT rc.RDB$RELATION_NAME AS "fk_table",
flds_fk.rdb$field_name as "fk_field",
rc2.rdb$relation_name as "pk_table",
flds_pk.rdb$field_name as "pk_field",
rc.RDB$CONSTRAINT_NAME AS "constraint_name",
rfc.RDB$CONST_NAME_UQ as "to_index",
flds_fk.rdb$field_position as "position"
FROM RDB$RELATION_CONSTRAINTS AS rc
LEFT JOIN rdb$ref_constraints AS rfc ON rc.RDB$CONSTRAINT_NAME = rfc.RDB$CONSTRAINT_NAME
LEFT JOIN rdb$index_segments AS flds_fk ON flds_fk.rdb$index_name = rc.rdb$index_name
LEFT JOIN rdb$relation_constraints AS rc2 ON rc2.rdb$constraint_name = rfc.rdb$const_name_uq
LEFT JOIN rdb$index_segments AS flds_pk ON flds_pk.rdb$index_name = rc2.rdb$index_name
AND flds_fk.rdb$field_position = flds_pk.rdb$field_position
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
AND rc.RDB$RELATION_NAME not like 'Z$%'
-- optionally select specific tables...
--AND rc.RDB$RELATION_NAME = 'tablename'
--AND rc.RDB$CONSTRAINT_NAME = 'fk_name'
ORDER BY rc.RDB$CONSTRAINT_NAME, flds_fk.rdb$field_position
</pre>

Interbase System Table Queries ( RDB$ ) - Revision history

WikiAdmin: 1 revision imported

Chanson at 21:09, 16 December 2021