RETRIEVE ALL DATABASE TABLES WITH ADO

// ******************************************************************
// RETRIEVE ALL DATABASE TABLES WITH ADO
// Category : ADO
// Author : Carlo Pasolini, Riccione
// Author Email : ccpasolini@libero.it
// Author Web :
// Tips Website : Swiss Delphi Center
// Tips Website URL: http://www.swissdelphicenter.ch
// ******************************************************************

//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;

unction 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;