Overview of system catalog tables

OpenEdge SQL maintains a set of system tables for storing information about tables, columns, indexes, constraints, and privileges.

All users have read access to the system catalog tables. SQL Data Definition Language (DDL) statements and GRANT and REVOKE statements modify system catalog tables. The system tables are modified in response to these statements, as the database evolves and changes.

The owner of the system tables is sysprogress. If you connect to a OpenEdge SQL environment with a username other than sysprogress, you must use the owner qualifier when you reference a system table in a SQL query. Alternatively, you can issue a SET SCHEMA sysprogress statement to set the default username for unqualified table names to sysprogress.

Core tables store information on the tables, columns, and indexes that make up the database. The remaining tables contain detailed information on database objects and statistical information.

The following table lists the system catalog tables in the same order that they are presented in following sections.

Table 1. System tables and descriptions
System table Summary description
SYSTABLES Core system table; one row for each TABLE in the database
SYSCOLUMNS Core system table; one row for each COLUMN of each table in the database
SYSINDEXES Core system table. One row for each component of each INDEX in the database
SYSBIGINTSTAT One set of rows for each BIGINT column in the database
SYSCALCTABLE A single row with a single column set to the value 100
SYSCHARSTAT One set of rows for each CHARACTER column in the database
SYSNCHARSTAT One set of rows for each NCHAR column in the database
SYSCHKCONSTR_NAME_MAP One row for each CHECK CONSTRAINT name mapping defined within the database schema
SYSCOLAUTH One row for each column for each user holding privileges on the column
SYSCOLSTAT Provides statistical information on data distribution
SYSCOLUMNS_FULL Superset of information in core system table SYSCOLUMNS
SYSDATATYPES Information on supported data types
SYSDATESTAT One set of rows for each DATE column in the database
SYSDBAUTH One row for each user with database-wide privileges
SYSFLOATSTAT One set of rows for each FLOAT column in the database
SYSIDXSTAT Information on indexes in the database
SYSINTSTAT One set of rows for each INTEGER column in the database
SYSNUMSTAT One set of rows for each NUMERIC column in the database
SYSPROCAUTH One or more rows for each stored procedure or User Defined Function which describes the user privileges to access these objects.
SYSPROCBIN One row for each compiled Java stored procedure or trigger in the database
SYSPROCCOLUMNS One row for each column in the result set of a stored procedure
SYSPROCEDURES One row for each stored procedure in the database
SYSPROCTEXT One row for each Java source code for a stored procedure or trigger in the database
SYSREALSTAT One set of rows for each REAL column in the database
SYSROLES One row for each role defined in the database, representing a named collection of privileges that can be granted to users or other roles
SYSSCHEMAS One row for each schema defined in the database, representing a namespace for organizing database objects such as tables, views, and procedures
SYSSEQAUTH One row for each unique user/sequence combination, holding sequence privileges on a sequence of the database
SYSSEQUENCES View of OpenEdge schema table_sequence
SYSSMINTSTAT One set of rows for each SMALLINT column in the database
SYSSQL_PROPERTIES One row for each SQL property setting in the database, used to store configuration and behavior flags for SQL processing
SYSSYNONYMS One row for each SYNONYM in the database
SYSTABAUTH One row for each unique user/table combination holding table privileges on a table in the database
SYSTABLES_FULL Superset of information in core system table SYSTABLES
SYSTBLSTAT Contains statistics for user tables in the database
SYSTIMESTAT One set of rows for each TIME column in the database
SYSTINYINTSTAT One set of rows for each TINYINT column in the database
SYSTRIGCOLS One row for each column specified in each trigger in the database
SYSTRIGGER One row for each trigger in the database
SYSTSSTAT One set of rows for each TIMESTAMP column in the database
SYSTSTZSTAT One set of rows for each TIMESTAMP WITH TIME ZONE column in the database
SYSNVARCHARSTAT One set of rows for each NVARCHAR column in the database
SYSVARCHARSTAT One set of rows for each VARCHAR column in the database
SYSVIEWS One row for each VIEW in the database
SYS_CHKCOL_USAGE One row for each CHECK CONSTRAINT defined on a column in the database
SYS_CHK_CONSTRS One row for each CHECK CONSTRAINT defined on a user table in the database
SYS_KEYCOL_USAGE One row for each column in the database defined with a PRIMARY KEY or FOREIGN KEY
SYS_REF_CONSTRS One row for each table in the database defined with a REFERENTIAL INTEGRITY CONSTRAINT
SYS_TBL_CONSTRS One row for each CONSTRAINT defined on a table in the database