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;