Wednesday, November 26, 2008

Tip #18 – Getting Table Information with SQL

As mentioned in tip #16 there are many system tables which provide schema information. For example the system.tables view returns information about the tables. It is important to note that this view only returns information about data dictionary bound (database) tables.

The system.tables view returns one row for each table in the data dictionary. It contains a lot of useful information about each table such as; the primary key, default index, encryption, validation expression and comments. The following example returns the table name and comment for all the tables in the data dictionary.

System.Tables Example

The table type is stored as an integer which correspond to to the table type constants defined in the ACE Header files. These table type constants are listed below:

  • ADS_NTX = 1  DBF with single tag index files
  • ADS_CDX = 2  DBF with compound indexes
  • ADS_ADT = 3  Advantage proprietary file format
  • ADS_VFP = 4  Visual FoxPro 9 DBF files
You can use a CASE statement to return a more meaningful description of the table type as shown below:

Show Table Type

No comments: