How do I test if a table exists in Interbase?

I have just recently started using Interbase, and I need to verify if the database has a table, how do I check in Interbase if a table exists in the database?

Solution

For IBX there are at least these ways disponible:

1. Using SQL query

You can query the RDB$RELATIONS system table in which you filter by the RDB$RELATION_NAME column. For example this query returns 1 when table called MyTable exists in the database:

SELECT 1 FROM RDB$RELATIONS
  WHERE RDB$RELATION_NAME = 'MyTable'

With IBX on client side you can write for instance this:

function TableExists(Database: TIBDatabase; const TableName: string): Boolean;
var
  Query: TIBSQL;
begin
  Query := TIBSQL.Create(Database);
  try
    Query.SQL.Text := 'SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = ?';
    Query.Params[0].AsString := TableName;
    Query.ExecQuery;
    { RecordCount reports only visited records, so it should be either 0 or 1 now }
    Result := Query.RecordCount > 0;
  finally
    Query.Free;
  end;
end;

This version is case sensitive and is efficient when you need to determine if table exists from code only ocassionally (for checking frequently I would cache the list of all table names returned by the GetTableNames method and query just such list).

2. Using TIBDatabase.GetTableNames method

The TIBDatabase class is able to list all the table names by the GetTableNames method. In the returned string list collection you can then verify if the name exists by the IndexOf method. For example:

function TableExists(Database: TIBDatabase; const TableName: string): Boolean;
var
  Tables: TStrings;
begin
  Tables := TStringList.Create;
  try
    Database.GetTableNames(Tables, True);
    Result := Tables.IndexOf(TableName) <> -1;
  finally
    Tables.Free;
  end;
end;

This version is case insensitive (so long you won't change the default value of the CaseSensitive property of the returned collection) and is naturally not as efficient as the first way for single or ocassional use because this one fetches the whole table name collection from server to client. But the GetTableNames method itself can be useful for frequent use if you cache the returned collection.

3. Using TIBExtract class

The TIBExtract IBX class is intended for fetching metadata. Unfortunately it't not so efficient and easy to use for just checking whether certain table exists in database because it can fetch either list of all tables, or details of the table itself. So I leave this option without example.