MySQL DESCRIBE TABLE

DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will use the DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command. Both DESCRIBE and DESC command are equivalent and case sensitive.

Syntax

The following are the syntax to display the table structure:

{DESCRIBE | DESC} table_name;

  

We can use the following steps to show all columns of the table:

Step 1: Login into the MySQL database server.

Step 2: Switch to a specific database.

Step 3: Execute the DESCRIBE statement.

Let us understand it with the help of an example that explains how to show columns of the table in the selected database.

Login to the MySQL Database

The first step is to login to the database server using the username and password. We should see the output as below image:

>mysql -u root -p  

Enter password: **********  

mysql>

Switch to a Specific Database

The next step is to open a particular database from which you want to display the table information using the following query. After the execution of a query, we should see the below output:

mysql> USE mysqltestdb 

Execute DESCRIBE Statement

It is the last step to display the table information. Before executing the DESCRIBE statement, we can optionally display all the tables stored in our selected database with the SHOW TABLES statement:

mysql> SHOW TABLES; 

For example, if we want to show a customer table’s structure, execute the below statement. After successful execution, it will give the output as below image:

mysql> DESCRIBE customer; 

We can also use the DESC statement for practice, which is a shorthand of the DESCRIBE command. See the below output:

How to display table information in MySQL Workbench?

To display the column information of the table in MySQL Workbench, we first need to launch the Workbench tool and login with the username and password to the MySQL database server. We will get the following screen:

Now do the following steps to show the table information:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. Select any database under the Schema menu, for example, mysqltestdb. It will pop up the multiple options that can be shown in the following image.

2. Next, click on the “Tables” that shows all tables stored in the mysqltestdb database. Select a table whose column information you want to display. Then, mouse hour on that table, it will show three icons. See the below image:

Now, click the icon (i) shown in the red rectangular box. It will display the following image:

Finally, click on the “Columns” menu to display the table structure.

MySQL SHOW COLUMNS Command

MySQL also allows the SHOW COLUMNS command to display table structure. It is a more flexible way to get columns information of a table.

Syntax:

The following are the syntax of the SHOW COLUMNS command:

mysql> SHOW COLUMNS FROM table_name; 

For example, if we execute the below query, we will get all columns information of a table in a particular database:

mysql> SHOW COLUMNS FROM customer; 

If we want to show the columns information of a table from another database or not available in the current database, we can use the following query:

mysql> SHOW COLUMNS FROM database_name.table_name;  

  

OR  

  

mysql> SHOW COLUMNS FROM table_name IN database_name;

In the below image, we can see that we had used the mysqltestdb database. But we had displayed the column’s information of a table from another database without switching to the current database.

If we want to display the more column information, we need to add FULL keyword with the SHOW TABLES statement as follows:

mysql> SHOW FULL COLUMNS FROM table_name

For example, the below SQL query lists all columns of the student_info table in the mystudentdb database:

mysql> SHOW FULL COLUMNS FROM student_info;  

After execution, we can see that this command adds the collation, privileges, default, and comment columns to the result set.

MySQL EXPLAIN

The EXPLAIN keyword is synonyms to the DESCRIBE statement, which is used to obtain information about how MySQL executes the queries. It can work with INSERT, SELECT, DELETE, UPDATE, and REPLACE queries. From MySQL 8.0.19 and later versions, it can also work with TABLE statements. When we use this keyword in queries, it will process the statement and provide the information about how tables are joined, the order of the table, estimated partitions and rows.

Example

If we want to show the execution plan of a SELECT statement, we can use the query as below:

mysql> EXPLAIN SELECT * FROM customer;  

Output:

This query produces the following information: