# Information Schema COLUMNS Table The [Information Schema](https://mariadb.com/docs/server/reference/system-tables/information-schema) `COLUMNS` table provides information about columns in each table on the server. It contains the following columns:
ColumnDescription
TABLE_CATALOGAlways contains the string 'def'.
TABLE_SCHEMADatabase name.
TABLE_NAMETable name.
COLUMN_NAMEColumn name.
ORDINAL_POSITIONColumn position in the table. Can be used for ordering.
COLUMN_DEFAULTDefault value for the column. Literals are quoted to distinguish them from expressions. NULL means that the column has no default.
IS_NULLABLEWhether the column can contain NULL values.
DATA_TYPEThe column's data type.
CHARACTER_MAXIMUM_LENGTHMaximum length.
CHARACTER_OCTET_LENGTHSame as the CHARACTER_MAXIMUM_LENGTH except for multi-byte character sets.
NUMERIC_PRECISIONFor numeric types, the precision (number of significant digits) for the column. NULL if not a numeric field.
NUMERIC_SCALEFor numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
DATETIME_PRECISIONFractional-seconds precision, or NULL if not a time data type.
CHARACTER_SET_NAMECharacter set if a non-binary string data type, otherwise NULL.
COLLATION_NAMECollation if a non-binary string data type, otherwise NULL.
COLUMN_TYPEColumn definition, a MySQL and MariaDB extension.
COLUMN_KEYIndex type. PRI for primary key, UNI for unique index, MUL for multiple index. A MySQL and MariaDB extension.
CREATE_OPTIONSExtra CREATE TABLE options.This column is available from MariaDB 13.0.
EXTRAAdditional information about a column, for example whether the column is an invisible column, or WITHOUT SYSTEM VERSIONING if the table is not a system-versioned table. A MySQL and MariaDB extension.
PRIVILEGESWhich privileges you have for the column. A MySQL and MariaDB extension.
COLUMN_COMMENTColumn comments.
IS_GENERATEDIndicates whether the column value is generated (virtual, or computed). Can be ALWAYS or NEVER.
GENERATION_EXPRESSIONThe expression used for computing the column value in a generated (virtual, or computed) column.
IS_SYSTEM_TIME_PERIOD_STARTFrom MariaDB 11.4.1.
IS_SYSTEM_TIME_PERIOD_ENDFrom MariaDB 11.4.1.
It provides information similar to, but more complete, than [SHOW COLUMNS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-columns) or output from [mariadb-show](https://mariadb.com/docs/server/clients-and-utilities/administrative-tools/mariadb-show). ## Examples ```sql SELECT * FROM information_schema.COLUMNS\G ... *************************** 9. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: t2 COLUMN_NAME: j ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: longtext CHARACTER_MAXIMUM_LENGTH: 4294967295 CHARACTER_OCTET_LENGTH: 4294967295 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_bin COLUMN_TYPE: longtext COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: IS_GENERATED: NEVER GENERATION_EXPRESSION: NULL ... ``` ```sql CREATE TABLE t ( s1 VARCHAR(20) DEFAULT 'ABC', s2 VARCHAR(20) DEFAULT (concat('A','B')), s3 VARCHAR(20) DEFAULT ("concat('A','B')"), s4 VARCHAR(20), s5 VARCHAR(20) DEFAULT NULL, s6 VARCHAR(20) NOT NULL, s7 VARCHAR(20) DEFAULT 'NULL' NULL, s8 VARCHAR(20) DEFAULT 'NULL' NOT NULL ); SELECT table_name, column_name, ordinal_position, column_default, column_default IS NULL FROM information_schema.COLUMNS WHERE table_schema=DATABASE() AND TABLE_NAME='t'; +------------+-------------+------------------+-----------------------+------------------------+ | table_name | column_name | ordinal_position | column_default | column_default IS NULL | +------------+-------------+------------------+-----------------------+------------------------+ | t | s1 | 1 | 'ABC' | 0 | | t | s2 | 2 | concat('A','B') | 0 | | t | s3 | 3 | 'concat(''A'',''B'')' | 0 | | t | s4 | 4 | NULL | 0 | | t | s5 | 5 | NULL | 0 | | t | s6 | 6 | NULL | 1 | | t | s7 | 7 | 'NULL' | 0 | | t | s8 | 8 | 'NULL' | 0 | +------------+-------------+------------------+-----------------------+------------------------+ ``` In the results above, the two single quotes in `concat(''A'',''B'')` indicate an escaped single quote - see [string-literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/string-literals). Note that while [mariadb client](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client/mariadb-command-line-client) appears to show the same default value for columns `s5` and `s6`, the first is a 4-character string "NULL", while the second is the SQL `NULL` value. {% hint style="info" %} The following statement is available from MariaDB 11.3. {% endhint %} ```sql CREATE TABLE t( x INT, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) ) WITH SYSTEM VERSIONING; SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, IS_SYSTEM_TIME_PERIOD_START, IS_SYSTEM_TIME_PERIOD_END FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t'\G *************************** 1. row *************************** TABLE_NAME: t COLUMN_NAME: x ORDINAL_POSITION: 1 IS_SYSTEM_TIME_PERIOD_START: NO IS_SYSTEM_TIME_PERIOD_END: NO *************************** 2. row *************************** TABLE_NAME: t COLUMN_NAME: start_timestamp ORDINAL_POSITION: 2 IS_SYSTEM_TIME_PERIOD_START: YES IS_SYSTEM_TIME_PERIOD_END: NO *************************** 3. row *************************** TABLE_NAME: t COLUMN_NAME: end_timestamp ORDINAL_POSITION: 3 IS_SYSTEM_TIME_PERIOD_START: NO IS_SYSTEM_TIME_PERIOD_END: YES ``` *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}