MySQL FAQ: How do I show the fields or schema of a database table?

MySQL FAQ: How do I show the schema of a MySQL database table?

Answer: Use the desc command from the MySQL command line client.

For instance, in my current application I have a database table named orders, and when I need to see the schema for that table I show it with the desc command like this:

desc orders

The MySQL output looks like this:

mysql> desc orders;

+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| price       | decimal(10,0)    | YES  |     | NULL              |                | 
| customer_id | int(10) unsigned | NO   | MUL | NULL              |                | 
| order_date  | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.02 sec)

The full process of displaying the schema of a MySQL database table

If you’re interested in seeing the full process from the time I login until the time I show the schema of this database table, here's what it looks like, with a few embedded comments:

#--------------------------------------------------
# I START WITH THE MYSQL LOGIN FROM MY LINUX PROMPT
#--------------------------------------------------

$linux_prompt> mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.51b MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


#---------------------------
# I DISPLAY ALL MY DATABASES
#---------------------------

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |
| pizza_store        | 
| sam                | 
| spanglish          | 
| test               | 
+--------------------+
6 rows in set (0.08 sec)


#-------------------------------------------------------
# I DECLARE THAT I WANT TO "USE" MY PIZZA_STORE DATABASE
#-------------------------------------------------------

mysql> use pizza_store;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


#------------------------------------------------
# I LIST ALL THE DATABASE TABLES IN THIS DATABASE
#------------------------------------------------

Database changed

mysql> show tables;

+-----------------------+
| Tables_in_pizza_store |
+-----------------------+
| crust_sizes           | 
| crust_types           | 
| customers             | 
| orders                | 
| pizza_toppings        | 
| pizzas                | 
| toppings              | 
+-----------------------+
7 rows in set (0.00 sec)


#----------------------------------------
# I SHOW THE SCHEMA FOR MY "ORDERS" TABLE
#----------------------------------------

mysql> desc orders;

+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| price       | decimal(10,0)    | YES  |     | NULL              |                | 
| customer_id | int(10) unsigned | NO   | MUL | NULL              |                | 
| order_date  | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.02 sec)


#-----------------------------------------------------
# I LOGOUT OF THE MYSQL CLIENT WITH THE "QUIT" COMMAND
#-----------------------------------------------------

mysql> quit

$linux_prompt> _

Summary

In summary, if you needed to see the fields or schema of a MySQL database table, I hope this is helpful.

Thank you!

Great blog, very visual, easy to understand. Thank you!

Post new comment

The content of this field is kept private and will not be shown publicly.