//How to retrieve all database tables with ADO
unit dbTables;
interface
uses ADODb;
type TTableType = (ttTable, ttView, ttSynonym, ttSystemTable, ttAccessTable);
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 ADODbTables(ADOConnection: TADOConnection; types: TTableTypes): TTableItems;
implementation
function addFilter(string1, string2: string): string; begin if string1 <> '' then Result := string1 + ' or ' + string2 else Result := string2; end;
function ADODbTables(ADOConnection: TADOConnection; types: TTableTypes): TTableItems; var ADODataSet: TADODataSet; i: integer; begin ADODataSet := TADODataSet.Create(nil); ADODataSet.Connection := ADOConnection; ADOConnection.OpenSchema(siTables, EmptyParam, EmptyParam, ADODataSet);
if (ttTable in types) then ADODataSet.Filter := addFilter(ADODataSet.Filter, '(TABLE_TYPE = ''TABLE'')');
if (ttView in types) then ADODataSet.Filter := addFilter(ADODataSet.Filter, '(TABLE_TYPE = ''VIEW'')');
if (ttSynonym in types) then ADODataSet.Filter := addFilter(ADODataSet.Filter, '(TABLE_TYPE = ''SYNONYM'')');
if (ttSystemTable in types) then ADODataSet.Filter := addFilter(ADODataSet.Filter, '(TABLE_TYPE = ''SYSTEM TABLE'')');
if (ttAccessTable in types) then ADODataSet.Filter := addFilter(ADODataSet.Filter, '(TABLE_TYPE = ''ACCESS TABLE'')');
ADODataSet.Filtered := True;
SetLength(Result, ADODataSet.RecordCount);
i := 0; with ADODataSet do begin First; while not EOF do begin with Result[i] do begin ItemName := FieldByName('TABLE_NAME').AsString; ItemType := FieldByName('TABLE_TYPE').AsString; end; Inc(i); Next; end; end;
ADODataSet.Free; end;
end.
{ Example: create a new project and add a TADOConnection (ADOConnection1), a TButton (Button1) and a TMemo (Memo1); assign a ConnectionString to the TADOConnection component and set "ADOConnection1.Active := True" }
procedure TForm1.Button1Click(Sender: TObject); var output: ttableitems; i: integer; begin output := ADODbTables(ADOConnection1, [ttTable, ttView, ttSynonym]); // output := ADODbTables(ADOConnection1, [ttSystemTable, ttAccessTable]); for i := Low(output) to High(output) do begin Memo1.Lines.Add(output[i].ItemName + '---' + output[i].ItemType); end; output := nil; end; |