COLUMNS

This view stores information about each column.

Column Name

Description

TABLE_CATALOG

The name of the catalog related to the table a given column belongs to.

TABLE_SCHEMA

The name of the related database (schema) for a given column.

TABLE_NAME

The name of the table a given column belongs to.

COLUMN_NAME

The name of the column.

ORDINAL_POSITION

The position of a given column among others in the table.

COLUMN_DEFAULT

The default value of a given column.

IS_NULLABLE

This value is YES if NULL values can be stored in the column, and NO if NULL values cannot be stored in the column.

IS_SPARSE

Whether or not sparse compression is used on a given column.

DATA_TYPE

The data type of a given column.

CHARACTER_MAXIMUM_LENGTH

The maximum number of characters for a given string column.

CHARACTER_OCTET_LENGTH

The maximum length, in bytes, for a given string column.

NUMERIC_PRECISION

The numeric precision (maximum number of digits present in a number) for a given column.

NUMERIC_SCALE

The numeric scale (maximum number of digits to the right the decimal point in a number) for a given column.

CHARACTER_SET_NAME

The name of the character set of a given column.

COLLATION_NAME

The collation name for the character set of a given column.

COLUMN_TYPE

The data type of a given column.

COLUMN_KEY

If this column is empty, the column is not indexed or the secondary column is a non-unique index. If the value in this column is MUL, multiple occurrences of the same value are allowed, and the column is the first included in a non-unique index. If the value of this column is PRI, it is either a primary key or one of multiple columns in a primary key. If the value of this column is UNI, it is the first column included in a unique index.

EXTRA

Whether a given column was created with additional options such as auto_increment, that are included in the column_definition section of the CREATE TABLE syntax.

PRIVILEGES

The privileges the current user has for a given column.

COLUMN_COMMENT

Comments (if any) from the definition of a given column.

DATETIME_PRECISION

The fractional-seconds precision of the column: 0 for DATETIME and 6 for DATETIME(6) columns, or NULL if the column not a time data type.

This includes TIME and TIMESTAMP.

Last modified: February 6, 2023

Was this article helpful?