I was looking at some old Delphi 7 code of mine when I was attempting to get in behind the scenes of Interbase and Firebird tables and fields to learn a little more about them. I came across these little pieces of information I'd like to share.
The following will list all the tables in one grid and, for each table, list all the fields in another grid. Drop on your TIBDatabase and direct it at your Interbase or Firebird server.
Now drop a TIBTransaction, TIBQuery, TDataSource, and a TDBGrid and connect them all.
Enter the following query into the IBQuery1...
SELECT DISTINCT RDB$RELATION_NAME as MyTable
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0
AND RDB$VIEW_CONTEXT IS NULL
ORDER BY RDB$RELATION_NAME
and make IBQuery1 live. Now drop on another TDBGrid, TIBQuery and TDataSource and connect them. This time, select IBQuery2 and add TDataSource1 in the "DataSource" Property. This will ensure that the second query will look to the first query to fill in the parameters. That parameter will be the field MyTable.
Enter the following query string into the SQL property of IBQuery2...
SELECT RDB$FIELD_NAME AS FIELDS
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = :MyTable
ORDER BY RDB$FIELD_POSITION
and make that query live. When you run that program, selecting tables in the first grid will show all the field information for that table in the second grid.
So far I have the following types in the field RDB$FIELD_TYPE. These can translate to...
- 8 = Integer
- 10 = Float
- 12 = Date
- 13 = Time
- 14 = Char
- 35 = TimeStamp
- 37 = VarChar
- 261 = Blob
There'll be a definitive list somewhere but that's all I have needed so far. I got that list from the internet somewhere but it was so long ago that I have forgotten where (thanks to whoever that was).
That will get you started, but here are some other queries that will return more metadata information...
Return indexes for a table
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$RELATION_NAME = :MyTable
AND RDB$UNIQUE_FLAG IS NULL
AND RDB$FOREIGN_KEY IS NULL
Return all Generators
SELECT RDB$GENERATOR_NAME
FROM RDB$GENERATORS
WHERE RDB$SYSTEM_FLAG IS NULL
Return all Triggers
SELECT * FROM
RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL
The shifters are coming in the morning so I'll post this now, please excuse any typing errors. Enjoy your day.
Remember: A positive attitude may not solve all your problems, but it will annoy enough people to make it worth the effort. Herm Albright (1876 - 1944)
