Retrieve all table names from an Interbase database (Views: 27)
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; |