Firebird - select master-detail

Question

Firebird - select master-detail

Navigation

#1 by (0 votes)

1

Hello. I need to query the system tables, so I get 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 make it up and for each field, Field_source and Field_type.

I am 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.

Would anyone point me to point the error in the above command, or else fix it? Thankful.

firebird

asked by anonymous 07.06.2016 / 03:28

1 answer

Measure RTT in JAVA Product of nasm matrices

score 0 · Accepted Answer

Could it be something like this?

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.