DelphiFAQ Home Search:

Retrieve all tables in a database with ADO

 

commentsThis article has not been rated yet. After reading, feel free to leave comments and rate it.

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!
Content-type: text/html

Comments:

2012-11-12, 05:33:28
anonymous from United States  
2015-02-01, 05:26:48
anonymous  
This piece was a liekajceft that saved me from drowning.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: