Hi there,

I would like to retrieve the following information from the system
tables of a Firebird 2.5 database:

- all table names (excluding views and system tables)
- all fields of the above-mentioned tables
- the length and type of the above-mentioned fields
- in case a field is a BLOB, I would like to know its subtype
- I would like to know if a field is computed or not

Currently, I am using the following query for this purpose:

SELECT RF.Rdb$Relation_Name,
RF.Rdb$Field_Name,
F.Rdb$Field_Length,
T.Rdb$Type_Name,
F.Rdb$Field_Sub_Type,
F.Rdb$Computed_Source
FROM Rdb$Relation_Fields RF
JOIN Rdb$Relations R ON RF.Rdb$Relation_Name = R.Rdb$Relation_Name
JOIN Rdb$Fields F ON F.Rdb$Field_Name = RF.Rdb$Field_Source
JOIN Rdb$Types T ON T.Rdb$Type = F.Rdb$Field_Type
WHERE R.Rdb$View_Blr IS NULL
AND (R.Rdb$System_Flag IS NULL OR R.Rdb$System_Flag = 0)
AND T.Rdb$Field_Name='RDB$FIELD_TYPE'
ORDER BY 1, RF.Rdb$Field_Position;

It shows me the field names and their corresponding table names, the
field length, the field types (in a quite unfamiliar manner, see
below) and the sub-type (which I ignore if a column is not a BLOB).
If Rdb$Computed_Source not null, I consider the field as computed.

This query seems to work for me, but I am not sure if all my
assumptions are correct. It would be great if someone who is
experienced with the system tables could have a look at it.

Regarding the field types, the table RDB$Types told me that there are
the following ones:

RB$TYPE RDB$TYPE_NAME my understanding
7 SHORT SMALLINT
8 LONG INTEGER
9 QUAD ?
10 FLOAT FLOAT
12 DATE DATE
13 TIME TIME
14 TEXT CHAR
16 INT64 BIGINT
27 DOUBLE DOUBLE
35 TIMESTAMP TIMESTAMP
37 VARYING VARCHAR
40 CSTRING I guess this is only used for UDFs?
45 BLOB_ID I guess this is used elsewhere?
261 BLOB BLOB

I would be very glad if somebody could shed some light on the items I
marked with questions marks.

Thank you in advance for your reply!


Cheers,
Christian