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:
- Login to the MySQL database server.
- Switch to a specific database.
- Use the
DESCRIBEstatement.
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 columns of a table, you specific 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;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)
In this tutorial, you have learned how to show the columns of a table by using MySQL SHOW COLUMNS command and DESC statement.