MySQL DESCRIBE – Get Detailed Information on Database Tables

By /
Mysql Describe Command

MySQL DESCRIBE query is used to describe a table. A short form of the DESCRIBE command is DESC. The DESCRIBE command is used to gain information about the table. It displays column names and the data type of every column along with the associated constraints.

Suppose you are taking a walk in the city and you see a structure. Let’s say the structure is a building. How would you describe it? You will describe its colour, the number of floors, the number and type of windows, the number of balconies and so on. Everything in the real world has a description. So how about describing tables in MySQL?

First of all, why would you need to describe a table? It may happen that you made a table and forgot about what columns you specified and their data types. It may also happen that you are working with someone and received a database to work on and are clueless about the constraints of the columns. 

Let’s get into understanding the syntax and the command usage.

Syntax of MySQL DESCRIBE

As mentioned earlier, you may either use the keyword DESCRIBE or just DESC. The syntax is as follows,

DESCRIBE table_name;Code language: SQL (Structured Query Language) (sql)

Or

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

Example of MySQL DESCRIBE Table

Let us understand how the DESCRIBE command works with the help of an example. Consider the following ‘Employee’ table. You can use the MySQL SELECT command to display a similar output for any of your other tables.

Employee Table
Employee Table

In this table, you see that column eid and Salary contains numbers, so their data type could be INT. Name and Department columns contain characters so their data type could either be VARCHAR or TEXT. And finally, the Date_Joined column contains dates so it’s data type could be DATE.

But what do you understand of any constraints on the columns just by looking at this table? Nothing. We can’t even be sure of the data types.

Using MySQL Describe on the Sample Table

Let us now use the MySQL Describe command as follows,

DESCRIBE Employee;Code language: SQL (Structured Query Language) (sql)

We get the output as follows,

Describe Table Command

Breaking down the DESCRIBE command output

  1. Field – Shows the names of the columns in our table.

As you can see, the output here is the names of the columns – eid, Name, Salary, Department and Date_Joined.

  1. Type – Shows the data types of the corresponding columns in our table.

The above output shows that columns eid and Salary are of the integer data type. Name is of the text data type. Department has varchar as its data type with a size of 255 characters. And lastly, Date_Joined is of the date data type.

  1. Null – Shows if the column can have a NULL value or not. A YES value means that the column can have a NULL value, and NO means column should not have a NULL value.

In the above output, except for the Date_Joined column, no other column can have a NULL value.

  1. Key – Shows the type of key a column is, if specified during table creation. 

In the above output, column eid has ‘PRI’ as the value in this column. It means that column eid is a Primary Key.

  1. Default – Shows the default value of the column.

As per the above output, only the Department column has a default value equal to ‘Operations’.

  1. Extra – Shows any other special information or constraints defined for the column.

The above output shows that when column eid was created, it was defined with an AUTO_INCREMENT option. That means that the value in eid gets automatically incremented for each successive record instead of being empty.

Conclusion

The MySQL DESCRIBE command can be very useful. MySQL makes itself very user-friendly by having commands like DESCRIBE which help the user understand the structure of a table. For further reading, you should check out the given link in the references.

References