|
The SYSTABCOL system view contains one row for each column of each table and view in the database. The underlying system table for this view is ISYSTABCOL.
| Column name | Column type | Column constraint |
|---|---|---|
| table_id | UNSIGNED INT | NOT NULL |
| column_id | UNSIGNED INT | NOT NULL |
| domain_id | SMALLINT | NOT NULL |
| nulls | CHAR(1) | NOT NULL |
| width | UNSIGNED INT | NOT NULL |
| scale | SMALLINT | NOT NULL |
| object_id | UNSIGNED BIGINT | NOT NULL |
| max_identity | BIGINT | NOT NULL |
| column_name | CHAR(128) | NOT NULL |
| "default" | LONG VARCHAR | |
| user_type | SMALLINT | |
| column_type | CHAR(1) | NOT NULL |
| compressed | TINYINT | NOT NULL |
| collect_stats | TINYINT | NOT NULL |
| inline_max | SMALLINT | |
| inline_long | SMALLINT | |
| lob_index | TINYINT |
table_id The object ID of the table or view to which the column belongs.
column_id The ID of the column. For each table, column numbering starts at 1. This numbering determines the order in which columns are returned by the SELECT command if an ORDER BY clause is not specified:
SELECT * FROM table
domain_id The data type for the column, indicated by a data type number listed in the SYSDOMAIN system view.
nulls (Y/N) Indicates whether NULL values are allowed in the column.
width The length of a string column, the precision of numeric columns, or the number of bytes of storage for any other data type.
scale The number of digits after the decimal point for NUMERIC or DECIMAL data type columns. For string columns, a value of 1 indicates character-length semantics and 0 indicates byte-length semantics.
object_id The object ID of the table column.
max_identity The largest value of the column, if it is an AUTOINCREMENT, IDENTITY, or GLOBAL AUTOINCREMENT column.
column_name The name of the column.
default The default value for the column. This value, if specified, is only used when an INSERT statement does not specify a value for the column.
user_type The data type, if the column is defined using a user-defined data type.
column_type The type of column (C=computed column, and R=other columns).
compressed Whether this column is stored in a compressed format.
collect_stats Whether the system automatically collects and updates statistics on this column.
inline_max The maximum number of bytes of a BLOB to store in a row. A NULL value indicates that either the default value has been applied, or that the column is not a character or binary type.
A non-NULL inline_max value corresponds to the INLINE value specified for the column using the CREATE TABLE or ALTER TABLE statement. For more information about the INLINE clause, see CREATE TABLE statement.
inline_long The number of duplicate bytes of a BLOB to store in a row if the BLOB size exceeds the inline_max value. A NULL value indicates that either the default value has been applied, or that the column is not a character or binary type.
A non-NULL inline_long value corresponds to the PREFIX value specified for the column using the CREATE TABLE or ALTER TABLE statement. For more information about the PREFIX clause, see CREATE TABLE statement.
lob_index Whether to build indexes on BLOB values in the column that exceed an internal threshold size (approximately eight database pages). A NULL value indicates either that the default is applied, or that the column is not BLOB type. A value of 1 indicates that indexes will be built. A value of 0 indicates that no indexes will be built.
A non-NULL lob_index value corresponds to whether INDEX or NO INDEX was specified for the column using the CREATE TABLE or ALTER TABLE statement. For more information about the [NO] INDEX clause, see CREATE TABLE statement.
PRIMARY KEY (table_id, column_id)
FOREIGN KEY (table_id) references SYS.ISYSTAB (table_id)
FOREIGN KEY (domain_id) references SYS.ISYSDOMAIN (domain_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id) MATCH UNIQUE FULL
FOREIGN KEY (user_type) references SYS.ISYSUSERTYPE (type_id)