DelphiFAQ Home Search:
General :: Programming :: Delphi :: Database
Database development with Delphi. Includes issues with BDE, ADO and InterBase.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Only the 40 most recently viewed articles are shown.
You can see the full list here.

Featured Article

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 
You don't like the formatting? Check out SourceCoder then!
Generated 16:01:25 on Jan 18, 2017