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
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/
