Which command is use to describe the table?

DESCRIBE means to show the information in detail. Since we have several tables in our SQL Server database, we will need a command to show a table's structure, such as column names, data types, constraints on column names, etc. SQL Server does not have any command to display the table structure like Oracle or MySQL provides DESCRIBE or DESC command. But we have some other way to describe a table or object in Microsoft SQL Server. This article gives a complete overview to display the table structure in SQL Server.

Permissions

We can display the table structure or properties for a table only when we have either owned the table or granted permissions to that table.

The following are the ways to display properties or structure of a table in SQL Server:

• Transact-SQL
• SQL Server Management Studio

Using Transact-SQL

SQL Server has extremely useful command or built-in system stored procedure sp_help similar to a 'help' command. We will get the detailed information regarding an object using this command. This command only returns information for objects that can be referenced inside the current database. We can use it as below:

Example

If we want to display properties of the 'Users' table, we need to use the below command:

It shows the following output:

As we can see in the image, it displays the creation time of the Users table, column names, data types, length, and constraints on the columns.

SQL Server can also use sp_columns stored procedure to show the structure of a SQL Server table. It is the simplest way to display the columns and related information of a selected table. We can use it as below syntax:

Example

To display the structure of a 'Users' table, we can use it as follows:

It returns the following output:

As we can see in the image, it returns a row for each column in the table that shows column information in detail.

If we do not want to use the stored procedure to describe the table structure, SQL Server also allows a query to accomplish this task. The following syntax can be used to show table information:

The INFORMATION_SCHEMA.COLUMNS statement produces information about all columns for all tables in a current database. By default, this information will be shown for each table in the database.

Example

If we want to get the table definition of a 'Users' table, we can use it as follows:

It will produce the following output:

Using SQL Server Management Studio

If we are using the SSMS, the following steps are used to describe the table definition:

Step 1: Connect to the database and navigate to the Object Explorer.

Step 2: Select the table for which you want to display the properties.

Step 3: Right-click on this table that displays various options and select Properties from the context menu.

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:

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:

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:

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:

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:

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:

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

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:

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:

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

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:

Output:

This query produces the following information:

How do you describe a table?

What is DESC command in SQL?

What is the command of table?

What is DESC command in Oracle?