The SYSCOLUMNS view contains one row for every column of each table and
view in the SQL schema (including the columns of the SQL catalog). The
following table describes the columns in the SYSCOLUMNS view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
COLUMN_NAME | NAME | VARCHAR(128) | Name of the column. This will be the SQL column name if one exists; otherwise, it will be the system column name. |
TABLE_NAME | TBNAME | VARCHAR(128) | Name of the table or view that contains the column. This will be the SQL table or view name if one exists; otherwise, it will be the system table or view name. |
TABLE_OWNER | TBCREATOR | VARCHAR(128) | The owner of the table or view. |
ORDINAL_POSITION | COLNO | INTEGER | Numeric place of the column in the table or view, ordered from left to right. |
DATA_TYPE | COLTYPE | VARCHAR(8) | Type of column:
|
LENGTH | LENGTH | INTEGER | The length attribute of the column; or, in the case of a decimal,
numeric, or nonzero precision binary column, its precision:
|
NUMERIC_SCALE | SCALE |
INTEGER Nullable | Scale of numeric data.
Contains the null value if the column is not decimal, numeric, or binary. |
IS_NULLABLE | NULLS | CHAR(1) | If the column can contain null values:
|
IS_UPDATABLE | UPDATES | CHAR(1) | If the column can be updated:
|
LONG_COMMENT | REMARKS |
VARCHAR(2000) Nullable | A character string supplied with the COMMENT statement.
Contains the null value if there is no long comment. |
HAS_DEFAULT | DEFAULT | CHAR(1) | If the column has a default value (DEFAULT clause or null capable):
|
COLUMN_HEADING | LABEL |
VARCHAR(60) Nullable | A character string supplied with the LABEL statement (column headings)
Contains the null value if there is no column heading. |
STORAGE | STORAGE | INTEGER | The storage requirements for the column:
|
NUMERIC_PRECISION | PRECISION |
INTEGER Nullable | The precision of all numeric columns.
Contains the null value if the column is not numeric. |
CCSID | CCSID |
INTEGER Nullable | The CCSID value for CHAR, VARCHAR, CLOB, DATE, TIME, TIMESTAMP, GRAPHIC,
VARGRAPHIC, DBCLOB, and DATALINK columns.
Contains 65535 if the column is a BINARY, VARBIN, BLOB, or ROWID. Contains the null value if the column is a numeric data type. |
TABLE_SCHEMA | DBNAME | VARCHAR(128) | The name of the SQL schema containing the table or view. |
COLUMN_DEFAULT | DFTVALUE |
VARCHAR(2000) Nullable | The default value of a column, if one exists. If the default value
of the column cannot be represented without truncation, then the value of the
column is the string 'TRUNCATED'. The default value is stored
in character form. The following special values also exist:
Contains the null value if:
|
CHARACTER_MAXIMUM_LENGTH | CHARLEN |
INTEGER Nullable | Maximum length of the string for binary, character and graphic string
data types.
Contains the null value if the column is not a string. |
CHARACTER_OCTET_LENGTH | CHARBYTE |
INTEGER Nullable | Number of bytes for binary, character and graphic string data
types.
Contains the null value if the column is not a string. |
NUMERIC_PRECISION_RADIX | RADIX |
INTEGER Nullable | Indicates if the precision specified in column NUMERIC_PRECISION is
specified as a number of binary or decimal digits
Contains the null value if the column is not numeric. |
DATETIME_PRECISION | DATPRC |
INTEGER Nullable | The fractional part of a date, time, or timestamp.
Contains the null value if the column is not a date, time, or timestamp. |
COLUMN_TEXT | LABELTEXT |
VARCHAR(50) Nullable | A character string supplied with the LABEL statement (column text)
Contains the null value if the column has no column text. |
SYSTEM_COLUMN_NAME | SYS_CNAME | CHAR(10) | The system name of the column |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | The system name of the table or view |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | The system name of the schema |
USER_DEFINED_TYPE_SCHEMA | TYPESCHEMA |
VARCHAR(128) Nullable | The name of the schema if this is a distinct type.
Contains the null value if the column is not a distinct type. |
USER_DEFINED_TYPE_NAME | TYPENAME |
VARCHAR(128) Nullable | The name of the distinct type.
Contains the null value if the column is not a distinct type. |
IS_IDENTITY | IDENTITY | VARCHAR(3) | This column identifies whether the column is an identity column.
|
IDENTITY_GENERATION | GENERATED |
VARCHAR(10) Nullable | This column identifies whether the column is GENERATED ALWAYS or
GENERATED BY DEFAULT.
Contains the null value if the column is not a ROWID or IDENTITY column. |
IDENTITY_START | START |
DECIMAL(31,0) Nullable | Starting value of the identity column.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_INCREMENT | INCREMENT |
DECIMAL(31,0) Nullable | Increment value of the identity column.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_MINIMUM | MINVALUE |
DECIMAL(31,0) Nullable | Minimum value of the identity column.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_MAXIMUM | MAXVALUE |
DECIMAL(31,0) Nullable | Maximum value of the identity column.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_CYCLE | CYCLE |
VARCHAR(3) Nullable | This column identifies whether the identity column values will continue
to be generated after the minimum or maximum value has been reached.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_CACHE | CACHE |
INTEGER Nullable | Specifies the number of identity values that may be preallocated for
faster access. Zero indicates that the values will not be
preallocated.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_ORDER | ORDER |
VARCHAR(3) Nullable | Specifies whether the identity values must be generated in order of the
request.
Contains the null value if the column is not an IDENTITY column. |
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.