FairCom DB SQL maintains a set of system tables for storing information about table spaces, tables, columns, indexes, constraints, and privileges.
FairCom DB SQL data definition statements and GRANT and REVOKE statements update system catalog tables. Users have read access to the system catalog tables. The database administrator has update access to the tables, but should avoid modifying them directly.
There are two types of tables in the system catalog: base tables and extended tables. Base tables store the information on the table spaces, tables, columns, and indexes that make up the database. There are four system tables:
The rest of the system catalog tables are extended tables. They contain detailed information on database objects and statistical information.
The owner of the system tables is admin. If you connect to a FairCom DB SQL environment with a User ID other than admin, you need to qualify references to the tables in FairCom DB SQL queries. For example:
SELECT * FROM ADMIN.SYSTABLES
The following table shows details of the columns in each system table. Here is the FairCom DB SQL query that generated the data for this table. You can modify it to generate a similar list that includes user-created tables by omitting the line: and st.tbltype = ‘S’.
select sc.tbl 'Table', sc.col 'Column',
sc.coltype 'Data Type', sc.width 'Size'
from admin.syscolumns sc, admin.systables st
where sc.tbl = st.tbl
and st.tbltype = 'S'
order by sc.tbl, sc.id