Use SHOW COLUMNS to view all columns of a table in MySQL

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

List all columns in a table using the DESC statement
List all columns in a table using the SHOW COLUMNS statement

This article describes the usage of the SHOW COLUMNS statement.

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]

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 whose clolums do you want to list.
The FULL Keyword are used to output more information, including 3 more columns: Collation, Privileges and Comment. It is optional.
The LIKE pattern is optional, it is used to filter the column names according to the pattern.

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: Index definition for this column
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.

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: ********
```
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.

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 |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

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.

Use the USE command to set the default database:
```
USE sakila;
```
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.

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

SHOW COLUMNS usage

Returned columns

MySQL DESC Examples

Conclusion

On this page