Tables, fields, and foreign keys

A complicated SQL query I had to work out to tell me information about the current database.

This query will tell you
TableName, FieldName, FieldType, FieldSubType, ForeignTableName, ForeignFieldName

select distinct
  Rel.rdb$relation_name TableName,
  Rel.rdb$field_name FieldName,
  Fld.rdb$field_type FieldType,
  Fld.rdb$field_sub_type FieldSubType,
  FCon.rdb$Relation_Name ForeignTableName,
  FIseg.rdb$Field_Name ForeignFieldName
from
  rdb$relation_fields Rel
  left join
    rdb$relation_constraints Con
  on
    (Con.rdb$relation_name = Rel.rdb$relation_name and
     Con.rdb$constraint_type like 'FOREIGN%')
  left join
    rdb$indices IDX
  on
    IDX.rdb$index_name = Con.rdb$index_name


  left join
    rdb$index_segments ISeg
  on
    (ISeg.rdb$index_name = Idx.rdb$index_name and
     ISeg.rdb$Field_Name = Rel.rdb$field_name)
  left join
    rdb$Relation_Constraints FCon
  on
    FCon.rdb$index_name = Idx.rdb$Foreign_Key
  left join
    rdb$index_segments FIseg
  on
    (FISeg.rdb$index_name = Idx.rdb$Foreign_key and
     FISeg.rdb$Field_Position = ISeg.rdb$Field_Position),
  rdb$fields Fld,
  rdb$Relation_Fields RFld
where
  Rel.rdb$relation_name not like 'RDB$%' and
  Fld.rdb$field_name = Rel.rdb$field_source and
  RFld.rdb$Relation_Name = Rel.rdb$Relation_name and
  RFld.rdb$field_name = Rel.rdb$field_name
order by
  Rel.rdb$relation_name,
  RFld.rdb$Field_ID;

 

Share this article!

Follow us!

Find more helpful articles: