Menu

Display all columns of a table using DESC in MySQL

Use MySQL DESC or DESCRIBE to inspect a table’s columns, types, nullability, indexes, defaults, and extra attributes.

Use MySQL DESC or DESCRIBE to list a table’s columns. For filters and additional output fields, use SHOW COLUMNS. See the MySQL SHOW COLUMNS reference for details about the returned fields.

MySQL DESC usage

To display all the columns of a table, use the DESC statement as the following syntax:

DESC [database_name.]table_name

Here,

  • The database_name is the name of the database. It can be omitted when you have selected a database as the default database.
  • The table_name is the name of the table.

You can also use DESCRIBE instead DESC. DESC is an abbreviation for DESCRIBE, they are exactly the same.

If you omit the database name before selecting a default database, MySQL returns Error 1046 (3D000). Qualify the table name or use USE first; see Error 1046 troubleshooting.

returned column

The MySQL DESC statement returns 6 columns:

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
Index/Key definition for this column
Default
The default value for this column
Extra
Other additional information. Such as auto_increment.

MySQL DESC Examples

The following example shows how to use the DESC statement to show all columns of the actor table in the Sakila sample database.

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

    mysql --user=admin_user --password

    Enter the password for admin_user and press Enter:

    Enter password: ********

  2. Just run the following command to try to list all the tables:

    DESC 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 names in the DESC statement:

    DESC 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 USE command to set the default database:

    USE sakila;

  5. Just run the following command to try to list all the tables:

    DESC actor;

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

Conclusion

In this article, you learned how to use the DESC statement. The DESC statement is shortcut commands for SHOW COLUMNS. If you want to filter the results or display more information, please use SHOW COLUMNS statement to list the columns of the table.