DESC Command in SQL

What is DESC Command in SQL?

As the name suggests, DESCRIBE means to show the information in detail. As we have several tables in our database, we will need a command that shows a table's structure, i.e., its column names, data types, constraints on column entries, etc. The describe table command in SQL will solve this problem.

We can either use DESC or DESCRIBE (both are case insensitive) statements, and both will return the same result.

Let us consider a table named student_marks. We can use the DESC or DESCRIBE command to find the fields of this table. Let's say the given table has three columns named ROLL_NO, NAME and MARKS. The same info can be obtained by running the desc table command as shown below:

Output:

Here, describe command shows the structure of the table, which includes the column's name, the column's data type, and the nullability (which means that column can contain null values or not). All of these features of the table are defined at the time of the creation of the table.

Syntax of DESC Command in SQL

SQL DESCRIBE table command uses the following syntax:

OR

We can provide the name of the table of which we want to know the details of its schema. For the above example, the command used to describe the schema of student_marks table is:

OR

The following is another syntax for describing the table, which can also help us specify some additional information.

Parameters of DESC Command in SQL

format: Specifies the additional information about the desired output. For example, if the format is “EXTENDED”, then the output also contains additional metadata such as parent database, owner etc.

table_identifier:

Specifies a table name in the database for which we want to show the structure.

partition_spec:

An optional parameter that specifies a comma-separated list of key and value pairs for partitions.

col_name:

An optional parameter that specifies the column name that needs to be specifically described.

Examples of DESC Command in SQL

Assume that we have a table named student_details in our database created as follows:

After completing the structure of the table, let us fill in some records as below:

Querying using SQL to find the details of this table by the following query:

OR

Output:

As you can notice, a default value for the MARKS column was added while creating the table. Therefore, in the above output, the DEFAULT field has 0 in the MARKS row. Similarly, for the ROLL_NO row, you can see the auto_increment value in the extra column. Note that the YES in the NULL column says that the value for that specific table column can be NULL and NO denotes that we cannot place or insert NULL values.

Let's see another example with the same student_details table created above(let's assume that the database which is storing this table is named as students), and here we will also include some additional information about the table so that we can understand the use of format, partition_spec, col_name with the describe command.

• Case 1: Returns basic schema of table student_details.

In our case

Output:

• Case 2: Returns basic schema of table student_details in students database.

In our case

Output:

• Case 3: Returns the metadata for the FIRST_NAME column.

In our case

• Case 4: Returns additional metadata such as parent database, owner, access time etc.

In our case

Output: