SHOW COLUMNS

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

The SHOW COLUMNS statement provides details about the columns in a specified table, including views. It only shows information for columns for which you have privileges.

If the optional EXTENDED keyword is used, the output includes details about hidden columns that are used internally by MySQL but aren’t accessible by users.

The FULL keyword, when used, adds column collation, comments, and privileges information for each column to the output.

The LIKE clause, if included, filters the column names based on the specified pattern. The WHERE clause can also be used to apply broader selection criteria.

The following table lists the SHOW COLUMNS output values.

Value Description
Field The name of the column.
Type The data type of the column.
Collation The collation for nonbinary string columns. NULL for other columns. This is shown only if the FULL keyword is used.
Null Indicates whether the column allows NULL values: YES if it does, NO if not.
Key Indicates whether the column is indexed. Possible values:
  • Empty – The column isn’t indexed or is part of a secondary column in a nonunique index.
  • PRI – The column is part of the primary key.
  • UNI – The column is the first column in a unique index.
  • MUL – The column is the first column in a nonunique index.

Priority order: PRI, UNI, MUL.
A unique index may be shown as PRI if it doesn’t allow NULL and there’s no primary key, or as MUL if part of a composite unique index.
Default The default value for the column. NULL if the column has an explicit default of NULL or no DEFAULT clause.
Extra Additional information about the column, such as:
  • auto_increment – For columns with the AUTO_INCREMENT attribute.
  • on update CURRENT_TIMESTAMP – For TIMESTAMP or DATETIME columns with the ON UPDATE CURRENT_TIMESTAMP attribute.
  • VIRTUAL GENERATED or STORED GENERATED – For generated columns.
  • DEFAULT_GENERATED – For columns with an expression default value.
Privileges The privileges you have for the column. This is shown only if the FULL keyword is used.
Comment The comment included in the column definition. This is shown only if the FULL keyword is used.

Information about table columns is also available through the INFORMATION_SCHEMA.COLUMNS view. By default, SHOW COLUMNS includes a table’s generated invisible primary key (GIPK), if one exists; to omit this column from the output, set the show_gipk_in_create_table_and_information_schema system variable to OFF.

For more information, see the MySQL documentation: SHOW COLUMNS Statement.