View source for Interbase System Table Queries ( RDB$ )

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>