There are five system tables that provide you with information about your dictionary-bound tables: system.tables, system.columns, system.indexes, system.indexfiles, and system.triggers. The table system.tables contains one record for each table in your data dictionary. (Views are not listed in this table. You can get view information from the system.views table.) Columns in this table contain information about a table's name, physical location on disk, encryption, permissions level, primary key, and record-level constraint, to name a few.

The following query retrieves information about all nonsystem tables in the data dictionary:

SELECT * FROM system.tables

The next query returns one record with information about the PRODUCTS table:

SELECT * FROM system.tables
 WHERE NAME = 'PRODUCTS'

The system.columns table contains one record for every field in every table in your data dictionary. Fields in this table include the name of the field, the name of the table to which the field belongs, the ordinal position of the field in its table structure, column type, constraints, and so forth.

The values in the Field_type column are integer codes. Table 14-1 contains the values for these codes.

 

Table 14-1: Field Type Codes