Mega Code Archive
Retrieve all tables in a database with ado
<![CDATA[
The following code enumerates all tables and views in a database. ADO distinguishes between these table types:
Table
View
Synonym
System Table
Access Table
The supplied unit defines matching constants and function ADODbTables can be used as shown in the button click handler at the bottom.
unit dbTables;
// Retrieve all tables in a database with ADO
interface
uses
ADODb;
type
TTableType = (ttTable, ttView, ttSynonym, ttSystemTable, ttAccessTable);
TTableTypes = set of TTableType;
TTableItem = record
ItemName: string;
ItemType: string;
end;
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 { addFilter }
if string1<>'' then
Result := string1 + ' or ' + string2
else
Result := string2
end; { addFilter }
function ADODbTables(ADOConnection: TADOConnection; types: TTableTypes) : TTableItems;
var
ADODataSet: TADODataSet;
i : integer;
begin { ADODbTables }
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; { with Result[i] }
Inc(i);
Next
end; { not EOF }
end; { with ADODataSet }
ADODataSet.Free
end; { ADODbTables }
end.
// ===============================================================================
//
// Example how to use this unit:
//
// 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
]]>