create table SYS_COLS (
                TABLE                   varchar,
                COLUMN                  varchar,
                COL_ID                  integer,
                COL_DTP                 integer,
                primary key (COL_ID))

        create index SYS_COLS_BY_NAME on SYS_COLS (TABLE, COLUMN)

        create table SYS_KEYS (
                KEY_TABLE               varchar,
                KEY_NAME                varchar,
                KEY_ID                  integer,
                KEY_N_SIGNIFICANT       integer,
                KEY_CLUSTER_ON_ID       integer,
                KEY_IS_MAIN             integer,
                KEY_IS_OBJECT_ID        integer,
                KEY_IS_UNIQUE           integer,
                KEY_MIGRATE_TO          integer,
                primary key (KEY_TABLE, KEY_NAME))

        create index SYS_KEYS_BY_ID on SYS_KEYS (KEY_ID)

        create table SYS_KEY_PARTS (
                KP_KEY_ID               integer,
                KP_NTH                  integer,
                KP_COL                  integer
                primary key (KP_KEY_ID, KP_NTH))

        create table SYS_KEY_SUBKEY (
                SUPER                   integer,
                SUB                     integer,
                primary key (SUPER, SUB))

The SYS_COLS table defines all tables and columns. Each column appears once in this table. No entries are made for inherited columns. The SYS_KEYS table defines all indices. A row in this table corresponds to each key in the database, either originally defined or inherited.

KEY_IS_MAIN is non-zero if this is the primary key of its table. The KEY_MIGRATE_TO is the key ID of a new version of this key if this key is obsolete (e.g. the primary key of a table from before an ALTER TABLE). The KEY_N_SIGNIFICANT indicates how many leading key parts are used in ordering keys.

The KEY_ID references the KP_KEY_ID in the SYS_KEY_PARTS table. This table embodies the actual layout of keys and rows. The KP_NTH is a number positioning the KP_COL to the appropriate place in the row. The KP_COL references the COL_ID in SYS_COLS. The KP_NTH is not necessarily a series of consecutive integers but the order matches the order of columns on the row.

The SYS_KEY_SUBKEY table has a row for each pair of keys where one is the immediate subkey of the other. Note that when a table is altered, the obsolete layout is marked as a subtable of the new layout of the primary key. Thus a select on the new primary key will also get the obsolete rows.

The following SQL procedure exemplifies how the SYS_COLS, SYS_KEYS and SYS_KEY_PARTS tables combine. Given a table name it produces the set of columns and the set of indices and their parts.

        create procedure dt (in tn varchar)
        {
                declare index_name, col, dtp varchar;
                declare t, nth integer;

                dtp := '-';

                result_names (col, t);    --- first result set

                declare cr cursor for select COLUMN, COL_DTP
                  from SYS_COLS where TABLE = tn;

                whenever not found goto done;
                open cr;
                while (1=1) {
                        fetch cr into col, t;
                        result (col, t);
                }
        done:
                result_names (index_name, nth, col);     -- 2nd result set
                end_result ();

                declare icr cursor for select KEY_NAME, KP_NTH, COLUMN
                  from SYS_KEYS, SYS_KEY_PARTS, SYS_COLS
                  where KEY_TABLE = tn and KP_KEY_ID = KEY_ID
                  and KP_NTH < KEY_N_SIGNIFICANT and  COL_ID = KP_COL;

                whenever not found goto k_done;
                open icr;
                while (1=1) {
                        fetch icr into index_name, nth, col;
                        result (index_name, nth, col);
                }
        k_done:
                return 0;
        }