In MySQL we can use the SHOW COLUMNS statement to get information about the columns in a table or view.

We can narrow the columns down to just a certain table and database. We can also filter by column name using a pattern.

The SHOW COLUMNS statement only shows information about columns that we have some privilege.

Syntax

The official syntax goes like this:

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

Below are some examples of how it works.

Example

Here’s an example to demonstrate the basic usage:

SHOW COLUMNS FROM actor;

Result:

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

Here, I got all columns from the actor table. Each row is for each column in the table. The Field column contains the column names. The other columns contain things like data type, whether the column is nullable, its default value, and any extra information, such as whether it’s generated, auto incremented, etc. For any invisible columns, we’d see INVISIBLE included in the Extra column.

Filter By Database

We also have the option of specifying the database:

USE sakila;
SHOW COLUMNS FROM pets FROM pethotel;

Result:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| PetId     | int         | NO   | PRI | NULL    |       |
| PetTypeId | int         | NO   |     | NULL    |       |
| OwnerId   | int         | NO   |     | NULL    |       |
| PetName   | varchar(60) | NO   |     | NULL    |       |
| DOB       | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

To demonstrate the effect, I first ran the USE command to switch to the sakila database. I then specified a different database (the pethotel database) in my SHOW COLUMNS statement.

In other words, I got information about a column that was in a different database to my current database.

Filter by Column Name

We can also use a pattern to filter the results to just certain columns:

SHOW COLUMNS FROM actor LIKE 'last%';

Result:

+-------------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type        | Null | Key | Default           | Extra                                         |
+-------------+-------------+------+-----+-------------------+-----------------------------------------------+
| last_name   | varchar(45) | NO   | MUL | NULL              |                                               |
| last_update | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------------------------+

Filter by Other Criteria

We can also use the WHERE clause to narrow the results based on other criteria:

SHOW COLUMNS FROM actor WHERE Extra LIKE '%GENERATED%';

Result:

+-------------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type      | Null | Key | Default           | Extra                                         |
+-------------+-----------+------+-----+-------------------+-----------------------------------------------+
| last_update | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------------------------+

The EXTENDED and FULL Keywords

We can use the EXTENDED and/or FULL keywords to provide more information about the columns.

• The EXTENDED keyword causes the output to include information about hidden columns that MySQL uses internally and are not accessible by users.
• The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

Here’s an example that uses both:

SHOW EXTENDED FULL COLUMNS FROM actor;

Result:

+-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+
| 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 |         |
| DB_TRX_ID   |                   | NULL               | NO   |     | NULL              |                                               | select,insert,update,references |         |
| DB_ROLL_PTR |                   | NULL               | NO   |     | NULL              |                                               | select,insert,update,references |         |
+-------------+-------------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+

The FIELDS Keyword

The syntax allows us to use SHOW FIELDS instead of SHOW COLUMNS:

SHOW FIELDS FROM actor;

Result:

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

Alternatives to SHOW COLUMNS

MySQL also provides the DESCRIBE statement (and its synonyms DESC and EXPLAIN), which provides similar data. There’s also the information_schema.columns table that we can query for column information.