Retrieve-all-tables-in-a-database-with-ado


            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
Examples Delphi
