How to Describe a Table in SQL?

DESCRIBE or DESC in SQL is a statement that shows the structure of the table. It gives all the information of each of the columns of the specified table such as column name, column type, default value, if it is NULL or NOT NULL, etc. As SQL is case insensitive desc and describe or DESC and DESCRIBE convey the same meaning.

DESC command will return the following result for the CITY Table that contains city_code, city_name, and the state in which the city in present.

Syntax

To describe a table in SQL we first write DESC or DESCRIBE and the name of the table followed by a semicolon ;.

Syntax:

Example

SQL Query to create Agents table:

Insert values to the Agents table:

Printing Agents table:

Output:

Using DESC to retrieve the structure of the table:

Output:

• Field - It is the name of the column.
• Type - It is the data type of the values that a given column can contain, like varchar, int, float, etc.
• NULL - It tells if the column or field can contain null values. If they can contain NULL values then the column has YES else it says NO.
• Key - There are various types of keys in SQL, such as primary key, foreign key, etc. It mentions the type of key if any.
• Default - While defining the table, we can give the default value to the column. In case the value for that column is not entered then it will take the default value. If any default value exists then it will appear here.
• Extra - It returns additional information related to the table or query.

As in the above example, AGENT_CODE is a primary key and so it cannot be null, thus in the column NULL the value NO is present. In the rest of the columns, the column types with their precision, i.e. size of the column-like $6$, $20$, etc. are returned as described while creating the table. As we have not mentioned the default value for any of the columns, the default for all the columns is NULL initially.

What does the DESC Table Mean in SQL?

DESCRIBE in SQL is a command that explains the structure of the table. Actual databases are structured, programmed, and used by a group of people. Such scenarios describe functionality that makes it easier for multiple people to access the database.

The DESCRIBE command will show all the details,

• columns present in the table
• each column's current value types such as VARCHAR, CHAR, INT, FLOAT, DATE, etc.
• default values of columns where exists
• NULL or NOT NULL values that a column can contain.

Example of Describe Table in SQL

Let dive in and see a few examples of DESCRIBE query in SQL

Example 1 - Students Table

Student table in the following example stores ID, name, mentor_id, birth_year, and percentage as the data of the students.

Creating Students table:

Insert values to the Students table:

Printing STUDENTS table:

Output:

Using DESC to retrieve the structure of the table:

Output:

The table displays multiple columns with ID serving as the primary key and mentor_id as a unique identifier. The key column specifies the uniqueness of these columns. The data types used in the table include int, varchar, and char with specific precisions. As a primary key, ID must have a unique value for each record and cannot be null. The BIRTH_YEAR column has a default value of 2008, while the rest of the columns are initialized as NULL since no default value is mentioned.

Example 2 - Employee Table

Let us take another example of an employee table that stores the data of each employee. It stores EMP_ID, EMP_NAME, JOB_NAME, HIRE_DATE, MANAGER_ID, SALARY, and COMMISSION.

Creating Employees Table:

Inserting Values into Employees Table:

Printing Employee table :

Output:

Using DESC to retrieve the structure of the table:

Output:

In the Employee table, EMP_ID is designated as the primary key, while JOB_NAME is marked with the NOT NULL constraint, indicating it cannot have a null value. Therefore, these two columns must contain non-null values. The COMMISION column is the only one with a specified default value.

Example 3 - Customer and Order Table

Let us take another example of the Orders table and Customers table. The Orders table has a foreign key from the Customers table i.e. CUSTOMER_ID. We will explore what describe table tells us about the derived column.

Printing Table CUSTOMERS:

Output:

Using DESC to retrieve the structure of the table

Output:

The customer table is the same as the above examples, customer_id is the primary key and there are no default values.

Let us now define ORDERS table.

Printing table ORDERS:

Output:

Using DESC to retrieve the structure of the table

Output:

The Order_No column is the primary key in the orders table, while the customer_id column is a foreign key referencing the CUSTOMERS table. In the key column, MUL is assigned to customer_id, indicating it can hold multiple values. Moreover, Item and Price columns are constrained with the NOT NULL constraint, which means they cannot contain null values. Therefore, the Null column is marked as NO for Order_No (primary key), Item, and Price. For all the columns, the default value is set to NULL.