Author: Jonas Bilinkevicius 

Problem/Question/Abstract:

How to retrieve all table names from an Interbase database

Answer:
1   
2   unit InterbaseDbTables;
3   
4   interface
5   
6   uses
7     IbDatabase, IbCustomDataSet, SysUtils;
8   
9   type
10    TTableType = (ttTable, ttView, ttSystemTable);
11  
12  type
13    TTableTypes = set of TTableType;
14  
15  type
16    TTableItem = record
17      ItemName: string;
18      ItemType: string;
19    end;
20  
21  type
22    TTableItems = array of TTableItem;
23  
24  function addFilter(string1, string2: string): string;
25  function IbDbTables(IbDatabase: TIbDatabase; types: TTableTypes): TTableItems;
26  
27  implementation
28  
29  function addFilter(string1, string2: string): string;
30  begin
31    if string1 <> '' then
32      Result := string1 + ' or ' + string2
33    else
34      Result := string2;
35  end;
36  
37  function IbDbTables(IbDatabase: TIbDatabase; types: TTableTypes): TTableItems;
38  var
39    IbDataSet: TIbDataSet;
40    IbTransaction: TIbTransaction;
41    i: integer;
42    Filtro: string;
43  begin
44    IbDataSet := TIbDataSet.Create(nil);
45    IbTransaction := TIbTransaction.Create(nil);
46    IbTransaction.DefaultDatabase := IbDatabase;
47    IbDataSet.Transaction := IbTransaction;
48    IbDataSet.SelectSQL.Text := 'SELECT RDB$RELATION_NAME, RDB$SYSTEM_FLAG,
49      RDB$VIEW_SOURCE FROM RDB$RELATIONS'
50      if (ttTable in types) then
51      Filtro := addFilter(Filtro, '((RDB$VIEW_SOURCE IS NULL) and
52        ((RDB$SYSTEM_FLAG = 0) or (RDB$SYSTEM_FLAG is NULL)))'
53        if (ttView in types) then
54        Filtro := addFilter(Filtro, '(RDB$VIEW_SOURCE IS NOT NULL)');
55    if (ttSystemTable in types) then
56      Filtro := addFilter(Filtro,
57        '((RDB$SYSTEM_FLAG <> 0) and (RDB$SYSTEM_FLAG IS NOT NULL))');
58    if Filtro <> '' then
59      IbDataSet.SelectSQL.Text := IbDataSet.SelectSQL.Text + ' where ' + Filtro;
60    IbDataSet.Open;
61    IbDataSet.Last;
62    SetLength(Result, IbDataSet.RecordCount);
63    i := 0;
64    with IbDataSet do
65    begin
66      First;
67      while not Eof do
68      begin
69        with Result[i] do
70        begin
71          ItemName := Trim(FieldByName('RDB$RELATION_NAME').AsString);
72          if (not FieldByName('RDB$VIEW_SOURCE').IsNull) then
73            ItemType := 'VIEW'
74          else if (FieldByName('RDB$SYSTEM_FLAG').AsInteger <> 0) and
75            (not FieldByName('RDB$SYSTEM_FLAG').IsNull) then
76            ItemType := 'SYSTEM'
77          else
78            ItemType := 'TABLE';
79        end;
80        Inc(i);
81        Next;
82      end;
83    end;
84    IbDataSet.Close;
85    IbTransaction.CommitRetaining;
86    IbDataSet.Free;
87    IbTransaction.Free;
88  end;
89  
90  end.



Example:
Create a new project and add a TIbDatabase (IbDatabase1), a TButton (Button1) and a 
TMemo (Memo1). Assign the DatabaseName property of the 
91  IbDatabase1 
92  component and set "IbDatabase1.Connected := True".
93  
94  procedure TForm1.Button1Click(Sender: TObject);
95  var
96    output: TTableItems;
97    i: integer;
98  begin
99    output := IbDbTables(IbDatabase1, [ttTable, ttView]);
100   { output := IbDbTables(IbDatabase1, [ttView]);
101   output := IbDbTables(IbDatabase1, [ttSystemTable]); }
102   for i := low(output) to high(output) do
103   begin
104     Memo1.Lines.Add(output[i].ItemName + '---' + output[i].ItemType);
105   end;
106   output := nil;
107 end;