MySQL – Describe Table 

In this case, we need information about the structure of the table. Using the DESCRIBE statement, MySQL provides functionality for providing information about a table. A number of alternative statements are available in MySQL for describing a table, such as desc, which is the shorthand version of describe. This is also known as show columns, which are internal to the describe query.

Syntax

DESCRIBE name_of_table;

Or

DESC name_of_table;

Example of MySQL DESCRIBE table:

Let us consider an example. We have a table named employee in the Demo database. Let us now describe that table in more detail.

desc employees;

OUTPUT:

From the output, we can see that different details are provided for each column of the table. The field represents the column’s name; the type indicates its data type. Null represents whether the column allows NULL values. If Null is YES, then NULL values may be stored in that column, whereas if Null is NO, NULL values may not be stored there. A key indicates that a column is being indexed. The indexing method can be primary key indexing, foreign key indexing, or unique indexing. A default value is specified for the column as the value that will be assigned by default. During table creation, the AUTO_INCREMENTATION option is set to that column, and the extra field is set to an auto-increment value instead of a blank value.

To describe the employee table using the alternative of the DESC statement, let us use the following statement.

describe employees;

OUTPUT

EXPLAIN Statement to DESCRIBE Table

MySQL uses the EXPLAIN statement to describe complex queries, including the execution of each table within the query that may contain more than one table. It can also be used for SELECT, INSERT, UPDATE, and DELETE operations. In addition to describing the structure of the table, this statement can also be used.

explain employees;

OUTPUT: 

Observe that the EXPLAIN query produces the same result as the describe query.

Internal Execution of DESCRIBE Statement

In order to execute the describe query, we execute the same flow that is followed when we execute the show columns from that table name query.

Here is how the SHOW COLUMNS statement is constructed.

Syntax:

SHOW COLUMNS FROM name_of_table;

Name_of_table represents the name of the table whose column details are required.

show columns from employees;

OUTPUT:

When we are not using the same database to which the table belongs, we can also specify the database name, as shown in the query statement below.

SHOW COLUMNS FROM employees IN employees;

OUTPUT:

We can use the FULL clause in the SHOW COLUMNS statement to obtain additional information about the columns of the table. This is shown below in the query statement.

SHOW Full  columns from employees IN employees;