Skip to main content
Version: Latest-3.3

columns

columns contains information about all table columns (or view columns).

note

The metadata of synchronous materialized views is not recorded in columns. You can access it by executing SHOW PROC '/dbs/db/table/index_schema'.

The following fields are provided in columns:

FieldDescription
TABLE_CATALOGThe name of the catalog to which the table containing the column belongs. This value is always NULL.
TABLE_SCHEMAThe name of the database to which the table containing the column belongs.
TABLE_NAMEThe name of the table containing the column.
COLUMN_NAMEThe name of the column.
ORDINAL_POSITIONThe ordinal position of the column within the table.
COLUMN_DEFAULTThe default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.
IS_NULLABLEThe column nullability. The value is YES if NULL values can be stored in the column, NO if not.
DATA_TYPEThe column data type. The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.
CHARACTER_MAXIMUM_LENGTHFor string columns, the maximum length in characters.
CHARACTER_OCTET_LENGTHFor string columns, the maximum length in bytes.
NUMERIC_PRECISIONFor numeric columns, the numeric precision.
NUMERIC_SCALEFor numeric columns, the numeric scale.
DATETIME_PRECISIONFor temporal columns, the fractional seconds precision.
CHARACTER_SET_NAMEFor character string columns, the character set name.
COLLATION_NAMEFor character string columns, the collation name.
COLUMN_TYPEThe column data type.
The DATA_TYPE value is the type name only with no other information. The COLUMN_TYPE value contains the type name and possibly other information such as the precision or length.
COLUMN_KEYWhether the column is indexed:
  • If COLUMN_KEY is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.
  • If COLUMN_KEY is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
  • If COLUMN_KEY is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null column.)
  • If COLUMN_KEY is DUP, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
If more than one of the COLUMN_KEY values applies to a given column of a table, COLUMN_KEY displays the one with the highest priority, in the order PRI, UNI, DUP.
A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
EXTRAAny additional information that is available about a given column.
PRIVILEGESThe privileges you have for the column.
COLUMN_COMMENTAny comment included in the column definition.
COLUMN_SIZE
DECIMAL_DIGITS
GENERATION_EXPRESSIONFor generated columns, displays the expression used to compute column values. Empty for nongenerated columns.
SRS_IDThis value applies to spatial columns. It contains the column SRID value that indicates the spatial reference system for values stored in the column.