SQL Server Describe Table

SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. When working with databases in SQL Server it is essential to understand the schema of the tables present in the database. Describing a table means getting information about the structure and metadata of the table. In this article, we will learn how to describe a table in SQL Server.

There are 3 methods through which we can describe a table in SQL Server:

1. Using sp_help
2. Using sp_columns
3. Using INFORMATION_SCHEMA Views

Let’s set up an environment

To understand how we can get the describe a table SQL Server, we will consider the table Customer as shown below:

The sp_help is a system stored procedure provided by the SQL Server, which is a built-in utility that provides a detailed description of a table or a view in SQL Server. Following is the syntax to use sp_help procedure:

Syntax:

EXEC sp_help 'table_name';

where:

• EXEC: is used to execute the stored procedures.
• table_name: is the name of the table you want to describe.

Example

To describe the above table Customers using sp_help we will have to run the following query

EXEC sp_help 'Customers';

Output:

Explanation: The above query uses the system procedure help to return a detailed grid view to describe the table. The output table describes every detail for the table customer including the name of the columns, data type, precision, nullability, length, and many more information about the table customers.

The sp_columns is another system stored procedure provided by the SQL Server which can be used to describe a table. The sp_columns system procedures returns all the details about all of the columns present in a table . Following is the syntax to use sp_columns in SQL Server:

Syntax:

EXEC sp_columns 'table_name';

where:

• EXEC: is used to execute the stored procedures.
• table_name: is the name of the table you want to describe.

Example

To describe the above table Customers using sp_columns we will have to run the following query

EXEC sp_columns 'Customers';

Output:

Explanation: The following query returns an output table having 7 columns. The first 3 columns describe the ownership and the name of the table. The fourth column describes the name of each column present in the table, similarly, the next column describes the data type, type name, and precision for each column of the table.

To describe a table in SQL Server we can use the INFORMATION_SCHEMA view, which is a standard view provided by the SQL Server Management Studio belongs to the INFORMATION_SCHEMA database which is a special kind of database that stores the metadata of the databases, tables, columns, and other objects stored in the system. Following is the syntax to use INFORMATION_SCHEMA views to describe a table in SQL Server:

Syntax:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name';

where:

• table_name: is the name of the table you want to describe.
• column_name: describes all the columns of the table.
• data_type: describes data type of all the columns.
• character_maximum_length: denotes the maximum length a column can hold.
• is_nullable: denotes whether the column can have null values or not.

Example

To describe the above table Customers using INFORMATION_SCHEMA Views we will have to run the following query

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers';

Output:

Explanation: The following query returns an output table having 4 columns that describe the table Customers. The first column describes the name of the columns present in the table, the second column describes the data type of each column, the third column describes the maximum length of characters a column can hold and the last column denotes whether NULL values can be stored in the column or not.

In conclusion, SQL Server provides efficient tools for database management, including the ability to describe tables. Using sp_help, or sp_columns or using the INFORMATION_SCHEMA Views users can easily describe the table in their SQL Server databases and help in effective database administration and development.