The data dictionary Professional

All SQL databases have a model that describes how data is organized in the database. For example, a Sales database may be comprised of tables like Customer, Order and Order-Details. Each table, in turn, consists of columns like Firstname or CustomerID. In addition, a table has specific attributes such as primary-keys or foreign-keys and columns have associated attributes like indexed or not.

This model, along with other information such as privileges, is stored in a set of system tables called the database's catalog, also known as the Data dictionary. We will use the term Dictionary for consistency.

A data dictionary contains data describing data, it is a meta data structure. Since all data describing a database is stored in system tables, all information about the data model can be obtained from a DBMS using SQL SELECT statements. Unfortunately, the names of system tables can differ between DBMS vendors. To overcome this problem, the ODBCDBE recognizes a predefined set of DBMS independent system-table names, so that an application developer gains transparent access to the data dictionary. The syntax for dictionary table names is as follows:

@db.[table.]KEYWORD 

The @ sign indicates a meta data request. db. is the name of the database whose dictionary is accessed, table. stands for the name of the database table to retrieve meta data from and KEYWORD indicates the type of meta data. Note that data stored in the data dictionary is READ ONLY and cannot be changed by an Xbase++ application. The data dictionary can only be modified with a Database Administration Tool each DBMS is accompanied with.

The following code demonstrates how meta data can be inspected from an Xbase++ application. Assume a database named Sales containing a Customer table:

USE "@Sales.Customer.COLUMNS" 

AEVal( DbStruct(), {|a,i| QOut( a ,"=>", FieldGet(i) ) } ) 

CLOSE DATABASES 

The resulting output of this code as it was obtained from MS SQL Server is shown below (slightly reformatted). It lists the structure of the COLUMNS table and displays data stored in the first record. This data defines the first column in the Customer table, i.e. the database field holding the customer number (FIELD->CUSTNO):

{TABLE_CAT        , Q,    128,   0} => Sales 
{TABLE_SCHEM      , Q,    128,   0} => dbo 
{TABLE_NAME       , Q,    128,   0} => customer 
{COLUMN_NAME      , Q,    128,   0} => CUSTNO 
{DATA_TYPE        , N,      5,   0} =>     1 
{TYPE_NAME        , Q,    128,   0} => char 
{COLUMN_SIZE      , I,     10,   0} =>          6 
{BUFFER_LENGTH    , I,     10,   0} =>          6 
{DECIMAL_DIGITS   , N,      5,   0} =>     0 
{NUM_PREC_RADIX   , N,      5,   0} =>     0 
{NULLABLE         , N,      5,   0} =>     1 
{REMARKS          , M,    254,   0} => 
{COLUMN_DEF       , Q,   3000,   0} =>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
{SQL_DATA_TYPE    , N,      5,   0} =>     1 
{SQL_DATETIME_SUB , N,      5,   0} =>     0 
{CHAR_OCTET_LENGTH, I,     10,   0} =>          6 
{ORDINAL_POSITION , I,     10,   0} =>          1 
{IS_NULLABLE      , M,    254,   0} => YES 
{SS_DATA_TYPE     , N,      3,   0} =>  39