Display all columns of a table using DESC in MySQL

In MySQL, DESC statements are used to display all columns of a table.

In MySQL, DESC statements are used to display all columns of a table.

Sometimes, you may want to view information about all columns in a table. MySQL provides two commands to help you do this:

  1. Using DESC command to list all columns in a table
  2. Using SHOW COLUMNS statement to list all columns in a table

MySQL DESC statement is a simplified form of SHOW COLUMNS. This article mainly describes the usage of DESC statement.

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 do not specify the default database, and omit the database name, MySQL will return an error: ERROR 1046 (3D000): No database selected.

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 -u root -p

    Enter the password for the root account 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.