Retrieve all table names from an Interbase database

Problem/Question/Abstract:

How to retrieve all table names from an Interbase database

Answer:

unit InterbaseDbTables;

interface

uses
  IbDatabase, IbCustomDataSet, SysUtils;

type
  TTableType = (ttTable, ttView, ttSystemTable);

type
  TTableTypes = set of TTableType;

type
  TTableItem = record
    ItemName: string;
    ItemType: string;
  end;

type
  TTableItems = array of TTableItem;

function addFilter(string1, string2: string): string;
function IbDbTables(IbDatabase: TIbDatabase; types: TTableTypes): TTableItems;

implementation

function addFilter(string1, string2: string): string;
begin
  if string1 <> '' then
    Result := string1 + ' or ' + string2
  else
    Result := string2;
end;

function IbDbTables(IbDatabase: TIbDatabase; types: TTableTypes): TTableItems;
var
  IbDataSet: TIbDataSet;
  IbTransaction: TIbTransaction;
  i: integer;
  Filtro: string;
begin
  IbDataSet := TIbDataSet.Create(nil);
  IbTransaction := TIbTransaction.Create(nil);
  IbTransaction.DefaultDatabase := IbDatabase;
  IbDataSet.Transaction := IbTransaction;
  IbDataSet.SelectSQL.Text := 'SELECT RDB$RELATION_NAME, RDB$SYSTEM_FLAG,
    RDB$VIEW_SOURCE FROM RDB$RELATIONS';
    if (ttTable in types) then
    Filtro := addFilter(Filtro, '((RDB$VIEW_SOURCE IS NULL) and
      ((RDB$SYSTEM_FLAG = 0) or (RDB$SYSTEM_FLAG is NULL)))');
      if (ttView in types) then
      Filtro := addFilter(Filtro, '(RDB$VIEW_SOURCE IS NOT NULL)');
  if (ttSystemTable in types) then
    Filtro := addFilter(Filtro,
      '((RDB$SYSTEM_FLAG <> 0) and (RDB$SYSTEM_FLAG IS NOT NULL))');
  if Filtro <> '' then
    IbDataSet.SelectSQL.Text := IbDataSet.SelectSQL.Text + ' where ' + Filtro;
  IbDataSet.Open;
  IbDataSet.Last;
  SetLength(Result, IbDataSet.RecordCount);
  i := 0;
  with IbDataSet do
  begin
    First;
    while not Eof do
    begin
      with Result[i] do
      begin
        ItemName := Trim(FieldByName('RDB$RELATION_NAME').AsString);
        if (not FieldByName('RDB$VIEW_SOURCE').IsNull) then
          ItemType := 'VIEW'
        else if (FieldByName('RDB$SYSTEM_FLAG').AsInteger <> 0) and
          (not FieldByName('RDB$SYSTEM_FLAG').IsNull) then
          ItemType := 'SYSTEM'
        else
          ItemType := 'TABLE';
      end;
      Inc(i);
      Next;
    end;
  end;
  IbDataSet.Close;
  IbTransaction.CommitRetaining;
  IbDataSet.Free;
  IbTransaction.Free;
end;

end.


Example:
Create a new project and add a TIbDatabase (IbDatabase1), a TButton (Button1) and a TMemo (Memo1). Assign the DatabaseName property of the IbDatabase1 component and set "IbDatabase1.Connected := True".

procedure TForm1.Button1Click(Sender: TObject);
var
  output: TTableItems;
  i: integer;
begin
  output := IbDbTables(IbDatabase1, [ttTable, ttView]);
  { output := IbDbTables(IbDatabase1, [ttView]);
  output := IbDbTables(IbDatabase1, [ttSystemTable]); }
  for i := low(output) to high(output) do
  begin
    Memo1.Lines.Add(output[i].ItemName + '---' + output[i].ItemType);
  end;
  output := nil;
end;