MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

13.7.5.5 SHOW COLUMNS Syntax

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

SHOW COLUMNS displays information about the columns in a given table. It also works for views. SHOW COLUMNS displays information only for those columns for which you have some privilege.

mysql> SHOW COLUMNS FROM City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

An alternative to tbl_name FROM db_name syntax is db_name.tbl_name. These two statements are equivalent:

SHOW COLUMNS FROM mytable FROM mydb;
SHOW COLUMNS FROM mydb.mytable;

The optional FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 24.35, “Extensions to SHOW Statements”.

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

SHOW COLUMNS displays the following values for each table column:

Table column information is also available from the INFORMATION_SCHEMA COLUMNS table. See Section 24.5, “The INFORMATION_SCHEMA COLUMNS Table”.

You can list a table's columns with the mysqlshow db_name tbl_name command.

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

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