SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern']

SHOW COLUMNS displays information about the columns in a given table.

mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  |      | PRI | NULL    | auto_increment |
| Name       | char(35) |      |     |         |                |
| Country    | char(3)  |      | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  |      |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

If the data types differ from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in Section 12.1.5.1, “Silent Column Specification Changes”.

The FULL keyword can be used from MySQL 3.23.32 on. It causes the output to include the privileges you have for each column. As of MySQL 4.1, FULL also causes any per-column collation and comments to be displayed.

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. In other words, these two statements are equivalent:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW COLUMNS displays the following values for each table column:

Field indicates the column name.

Type indicates the column data type.

Collation indicates the collation for nonbinary string columns, or NULL for other columns. This value is displayed only if you use the FULL keyword.

The Null field indicates whether NULL values can be stored in the column, with YES displayed when NULL values are allowed.

The Key field indicates whether the column is indexed:

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

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.

If the column allows NULL values, the Key value can be MUL even when a UNIQUE index is used. The rationale is that multiple rows in a UNIQUE index can hold a NULL value if the column is not declared NOT NULL. (This behavior changes in MySQL 5.0.)

The Default field indicates the default value that is assigned to the column.

The Extra field contains any additional information that is available about a given column. The value is auto_increment for columns that have the AUTO_INCREMENT attribute and empty otherwise.

Privileges indicates the privileges you have for the column. This value is displayed only if you use the FULL keyword.

Comment indicates any comment the column has. This value is displayed only if you use the FULL keyword.

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See Section 12.7.1, “DESCRIBE Syntax”.

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables. See Section 12.4.5, “SHOW Syntax”.