SQL Server Describe 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.