Accessing Interbase/Firebird Metadata in Delphi

WARNING: Interbase and Firebird system tables are not for the faint hearted. It is not recommended that you alter anything in these tables if you ever want to use them again - and keep your job. Major stuff-ups can occur.

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)