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

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

Summary: in this tutorial, you will learn how to show 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>

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

mysql> USE classicmodels;
Database changed
mysql>

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)

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

DESC orders;

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;

To show 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;

Or

SHOW COLUMNS FROM table_name IN database_name;

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

SHOW COLUMNS FROM orders;

As you can see the result of this SHOW COLUMNS the 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;

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)

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

The SHOW COLUMNS the 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;

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)

In this tutorial, you have learned how to show the columns of a table by using MySQL SHOW COLUMNS command and DESC statement.