MySQL SHOW COLUMNS and DESCRIBE: List All Columns in a Table

Summary: in this tutorial, you will learn how to show the columns of a table by using the DESCRIBE statement and MySQL SHOW COLUMNS command.

Using DESCRIBE statement

To show all columns of a table, you use the following steps:

1. Login to the MySQL database server.
2. Switch to a specific database.
3. Use the DESCRIBE statement.

The following example demonstrates how to display columns of the orders table in the classicmodels database.

Step 1. Login to the MySQL database.

>mysql -u root -p
Enter password: **********
mysql>Code language: SQL (Structured Query Language) (sql)

Step 2. Issue the USE command to switch to the database to classicmodels:

mysql> USE classicmodels;
Database changed
mysql>Code language: SQL (Structured Query Language) (sql)

Step 3. Use the DESCRIBE statement.

mysql> DESCRIBE orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

In practice, you use the DESC statement which is a shorthand of the DESCRIBE statement. For example, the following statement is equivalent to the DESCRIBE above:

DESC orders;Code language: SQL (Structured Query Language) (sql)

MySQL SHOW COLUMNS command

The more flexible way to get a list of columns in a table is to use the MySQL SHOW COLUMNS command.

SHOW COLUMNS FROM table_name;Code language: SQL (Structured Query Language) (sql)

To show the columns of a table, you specify the table name in the FROM clause of the SHOW COLUMNS statement. To show columns of a table in a database that is not the current database, you use the following form:

SHOW COLUMNS FROM database_name.table_name;Code language: SQL (Structured Query Language) (sql)

Or

SHOW COLUMNS FROM table_name IN database_name;Code language: SQL (Structured Query Language) (sql)

For example, to get the columns of the orders table, you use the SHOW COLUMNS statement as follows:

SHOW COLUMNS FROM orders;
Code language: SQL (Structured Query Language) (sql)

As you can see the result of this SHOW COLUMNS command is the same as the result of the DESC statement.

To get more information about the column, you add the FULL keyword to the SHOW COLUMNS command as follows:

SHOW FULL COLUMNS FROM table_name;Code language: SQL (Structured Query Language) (sql)

For example, the following statement lists all columns of the payments table in the classicmodels database.

mysql> SHOW FULL COLUMNS FROM payments \G;
*************************** 1. row ***************************
     Field: customerNumber
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: checkNumber
      Type: varchar(50)
 Collation: latin1_swedish_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: paymentDate
      Type: date
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: amount
      Type: decimal(10,2)
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
4 rows in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

As you can see, the SHOW FULL COLUMNS command adds the collation, privileges, and comment columns to the result set.

The SHOW COLUMNS command allows you to filter the columns of the table by using the  LIKE operator or WHERE clause:

SHOW COLUMNS FROM table_name LIKE pattern;

SHOW COLUMNS FROM table_name WHERE expression;
Code language: SQL (Structured Query Language) (sql)

For example, to show only columns that start with the letter c, you use the LIKE operator as follows:

mysql> SHOW COLUMNS FROM payments LIKE 'c%';
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customerNumber | int(11)     | NO   | PRI | NULL    |       |
| checkNumber    | varchar(50) | NO   | PRI | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Finding tables that include a column

To find the table that has a column, you query data from the columns table of the information_schema database.

For example, the following statement finds all tables in the classicmodels database, which have the column orderNumber:

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'orderNumber';Code language: JavaScript (javascript)

Output:

+--------------+
| TABLE_NAME   |
+--------------+
| orders       |
| orderdetails |
+--------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the MySQL SHOW COLUMNS command and DESC statement to show the columns of a table.