Firebird - select master-Detail

Question

Hello. I need to make a query to the system tables, which return me the following result:

 Relation_Name, Field_Id, Field_Name, Field_source, Field_type

for each user-defined table (Relation_name) (except Views). That is, for each table, the fields (Field_name) that compose it and for each field, Field_source and Field_type.

I’m using the following command:

select distinct
             RelFields.RDB$Relation_Name   as "Rel Name",
             RelFields.RDB$Field_Id        as "Field Id",
             RelFields.RDB$Field_Name      as "Field Name",
             RelFields.RDB$Field_Source    as "Field Source",
                Fields.RDB$Field_Type      as "Field type"
from
                       RDB$Relations       as  Relations,
                       RDB$Relation_Fields as  RelFields
join
                       RDB$Fields          as     Fields
         on (Fields   .RDB$Field_Name    =     RelFields.RDB$Field_Name)
where
             Relations.RDB$Relation_Type = '0'                          and
             Relations.RDB$Relation_Name = RelFields.RDB$Relation_Name  and
  substring (Relations.RDB$Relation_Name    from 1 for 4)   <>   'IBE$' and
             Relations.RDB$System_Flag   = '0'

order by
             RelFields.RDB$Relation_Name,
             RelFields.RDB$Field_Name

but some Field_names are omitted randomly for certain tables.

Is it wonderful to point out the error in the above command, or to correct it? Thank you.

Answer 1

It would be something like that maybe?

select distinct RelFields.RDB$RELATION_NAME as "Rel Name", 
                RelFields.RDB$FIELD_ID as "Field Id",
                RelFields.RDB$FIELD_NAME as "Field Name",
                RelFields.RDB$FIELD_SOURCE as "Field Source",
                   Fields.RDB$FIELD_TYPE as "Field type"
from RDB$RELATIONS as RELATIONS
join RDB$RELATION_FIELDS as RelFields on 
     Relations.RDB$RELATION_NAME = RelFields.RDB$RELATION_NAME
join RDB$FIELDS as Fields on 
     Fields.RDB$FIELD_NAME = RelFields.RDB$FIELD_SOURCE
where Relations.RDB$VIEW_SOURCE is null and 
      Relations.RDB$SYSTEM_FLAG = 0 and 
      substring (Relations.RDB$Relation_Name from 1 for 4) <> 'IBE$'
order by RelFields.RDB$RELATION_NAME, RelFields.RDB$FIELD_NAME

In my opinion it would not be necessary to put the substring there at the end of SQL.