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;
	}