Menu

MySQL SHOW COLUMNS: List Table Columns and Types

Use MySQL SHOW COLUMNS or DESCRIBE to inspect column names, types, nullability, keys, defaults, and FULL output, with database and pattern examples.

Updated on

Use SHOW COLUMNS to inspect metadata for the columns of a MySQL table or view. DESCRIBE and its shorthand DESC provide the same basic column information. Use SHOW COLUMNS when you need its filtering options or the additional fields returned by FULL. See the MySQL 8.4 SHOW COLUMNS reference.

SHOW COLUMNS usage

To list all the columns of a table, use the SHOW COLUMNS statement. This is the syntax of MySQL SHOW COLUMNS:

SHOW [FULL] COLUMNS FROM [database_name.]table_name
    [LIKE 'pattern' | WHERE expr];

Here,

  • database_name is optional when the table belongs to the selected database; qualify the table name when you need to specify another database.
  • FULL adds the Collation, Privileges, and Comment fields to the result.
  • LIKE filters by a column-name pattern. WHERE can filter the result using other returned fields.
  • The result only includes columns for which the current account has some privilege.

Returned columns

The MySQL SHOW COLUMNS statement can return 9 columns, of which Collation, Privileges and Comment these 3 columns are returned only if the keyword FULL is used:

Field
The name of this column.
Type
The data type of this column.
Null
YES or NO indicates whether this column can be NULL.
Key
A key indicator for the column. Use SHOW INDEX to inspect index names, indexed columns, uniqueness, and order.
Default
Default value for this column
Extra
Other additional information. Such as auto_increment.
Collation
Collation for non-binary string columns, otherwise it is NULL.
Privileges
The privileges you have on this column.
Comment
Comment for this column

MySQL DESC Examples

The following example shows how to use MySQL SHOW COLUMNS statement list columns of the actor table in the Sakila sample database.

  1. Connect to the MySQL server using the mysql client tool:

    mysql -u root -p

    Enter the password for the root account and press Enter:

    Enter password: ********

  2. Just run the following command to try to show all the columns of the actor table:

    SHOW COLUMNS FROM actor;

    At this point, MySQL will return an error: ERROR 1046 (3D000): No database selected . Because you haven’t specified a default database.

  3. Specify the database and table name in the SHOW COLUMNS statement as following:

    SHOW COLUMNS FROM sakila.actor;

    Here is the output:

    +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

  4. Use the SHOW FULL COLUMNS statement to show complete information:

    SHOW FULL COLUMNS FROM sakila.actor;

    Here is the output:

    +-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+
| Field       | Type              | Collation          | Null | Key | Default           | Extra                                         | Privileges                      | Comment |
+-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+
| actor_id    | smallint unsigned | NULL               | NO   | PRI | NULL              | auto_increment                                | select,insert,update,references |         |
| first_name  | varchar(45)       | utf8mb4_0900_ai_ci | NO   |     | NULL              |                                               | select,insert,update,references |         |
| last_name   | varchar(45)       | utf8mb4_0900_ai_ci | NO   | MUL | NULL              |                                               | select,insert,update,references |         |
| last_update | timestamp         | NULL               | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references |         |
+-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+

    Here, compared to the above output, after using the FULL keyword, there are 3 more columns in the output: Collation, Privileges and Comment.

  5. Use the USE command to set the default database:

    USE sakila;

  6. Just run the following command to try to list all columns:

    SHOW FULL COLUMNS FROM actor;

    At this point, the output of this command is the same as SHOW FULL COLUMNS FROM sakila.actor;. This is because the default database is sakila now.

  7. Use LIKE clause to filter columns whose column names start with a:

    SHOW COLUMNS FROM actor LIKE 'a%';

    Here is the output:

    +----------+-------------------+------+-----+---------+----------------+
| Field    | Type              | Null | Key | Default | Extra          |
+----------+-------------------+------+-----+---------+----------------+
| actor_id | smallint unsigned | NO   | PRI | NULL    | auto_increment |
+----------+-------------------+------+-----+---------+----------------+

Conclusion

In this article, you learned how to use the SHOW COLUMNS statement :

  • Use the FULL keyword to output more information
  • Use LIKE clause to filter results by field name

For equivalent column-listing commands in other databases, see List Table Columns by Database.

Advertisement