SYSTABLES

Home | Previous Page | Next Page System Catalog Tables >

SYSTABLES

The systables system catalog table contains a row for each table object (a table, view, synonym, or in Dynamic Server, a sequence) that has been defined in the database, including the tables and views of the system catalog.

Column	Type		Explanation
tabname	VARCHAR(128)		Name of table, view, synonym, or (for IDS) sequence
owner	VARCHAR(32)		Owner of table (user informix for system catalog tables and username for database tables)
partnum	INTEGER		Physical location code
tabid	SERIAL		System-assigned sequential identifying number
rowsize	SMALLINT		Row size
ncols	SMALLINT		Number of columns in the table
nindexes	SMALLINT		Number of indexes on the table
nrows	INTEGER		Number of rows in the table
created	DATE		Date when the table was created
version	INTEGER		Number that changes when table is altered
tabtype	CHAR(1)		Code indicating the type of object: T = Table E = External Table V = View Q = Sequence (IDS) P = Private synonym S = Public synonym (Type S is not available in an ANSI-compliant database.)
locklevel	CHAR(1)		Lock mode for the table: B = Page P = Page R = Row T = Table (XPS)
npused	INTEGER		Number of data pages that have ever been initialized in the tablespace by the database server
fextsize	INTEGER		Size of initial extent (in kilobytes)
nextsize	INTEGER		Size of all subsequent extents (in kilobytes)
flags	SMALLINT		Codes for classifying permanent tables: ST_RAW ( = 0x00000010 ) (IDS) RAW ( = 0x00000002 ) (XPS) STATIC ( = 0x00000004 ) (XPS) OPERATIONAL ( = 0x00000008 ) (XPS) STANDARD ( = 0x00000010 ) (XPS) EXTERNAL ( = 0x00000020 ) (XPS)
site	VARCHAR(128)		Reserved for future use
dbname	VARCHAR(128)		Reserved for future use
type_xid (IDS)	INTEGER		Code from sysxtdtypes.extended_id for typed tables, or `0` for untyped tables
am_id (IDS)	INTEGER		Access method code (key to sysams table) NULL or 0 indicates built-in storage manager
minrowsize	SMALLINT	XPS	Minimum row size

Each table, view, sequence, and synonym recorded in the systables table is assigned a tabid, which is a system-assigned SERIAL value that uniquely identifies the object. The first 99 tabid values are reserved for the system catalog. The tabid of the first user-defined table object in a database is always 100.

The tabid column is indexed and contains only unique values. A composite index on the tabname and owner columns also requires unique values.

The version column contains an encoded number that is stored in systables when a new table is created. Portions of this value are incremented when data-definition statements, such as ALTER INDEX, ALTER TABLE, DROP INDEX, and CREATE INDEX, are performed on the table.

In the flags column, ST_RAW represents a nonlogging permanent table in a database that supports transaction logging.

When a prepared statement that references a database table is executed, the version value is checked to make sure that nothing has changed since the statement was prepared. If the version value has changed, the prepared statement is not executed, and you must prepare the statement again.

The npused column does not reflect the number of pages used for BYTE or TEXT data, nor the number of pages that are freed in DELETE operations.

The systables table has two rows that store information about the database locale: GL_COLLATE with a tabid of 90 and GL_CTYPE with a tabid of 91. To view these rows, enter the following SELECT statement:

SELECT * FROM systables WHERE tabid=90 OR tabid=91