How To Check Table Description In SQL Server

As a senior developer in SQL Server, I have identified a few best approaches to getting table descriptions. In this article, we will explore all the approaches.

Approach-1 Using the sp_help stored procedure

You can use the sp_help stored procedure to get the details of the SQL table mentioned in the query below.

Syntax

EXEC sp_help 'TableName'

Example

You can execute the query below to get the details of the order table.

EXEC sp_help 'Order'

After executing the above query, I got the expected output, as shown below.

Check out How to get table size in SQL Server

Approach-2 Using INFORMATION_SCHEMA.COLUMNS

Syntax

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table Name' AND TABLE_SCHEMA = 'table schema name'

Example

Let’s execute the SQL script below to get details about the Order table. This script will provide detailed information about each column in the Order table.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Order' AND TABLE_SCHEMA = 'dbo'

After executing the above query, we got the expected output below.

Check out How to Export SQL Server Table to CSV

Approach-3 Using sys.columns and sys.types

We can also use the sys.columns and sys.types for this purpose.

Syntax

SELECT c.name AS ColumnName, t.name AS DataType, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('TableName')

Example

You can execute the query below to get the details of the order table.

SELECT c.name AS ColumnName, t.name AS DataType, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Order')

After executing the above query, we successfully obtained the details of the table, as shown in the screenshot below.

Check out How To Find Column Name In Table SQL Server

Approach-4 Using SQL Server Management Studio (SSMS)

If you want to use the UI approach, you can get the table details using the SQL Server Management Studio (SSMS).

1. Log in to the SSMS and locate the table for the one you wish to get the details.

2. Right-click on the table name and select the properties option, as shown in the screenshot below.

The details are in the screenshot below. You can navigate to each tab to see the details.

Conclusion

In this article, we discussed different methods for viewing table descriptions in SQL servers, complete with examples and scenarios you might encounter in your daily work. Now, you can choose the best approach that suits you based on your requirement.

You may also like following the articles below.

• How To Rename The Table Name In SQL Server
• How To Update Statistics On A Table In SQL Server
• How To Enable CDC On A Table In SQL Server