Describe Table in SQL Server

by

In this comprehensive guide, I will walk you through exactly how to “describe” a table in SQL Server. Since T-SQL (Transact-SQL) handles this differently than other SQL dialects, I will show you the standard methods, the “insider” shortcuts, and the advanced querying techniques that will give you total command over your database schema.

Describe Table in SQL Server

In database terminology, describing a table means retrieving metadata about a table’s structure. This includes details such as:

  • Column names
  • Data types
  • Nullability (whether a column allows NULL values)
  • Default values
  • Constraints (primary keys, foreign keys, unique constraints)
  • Identity columns and indexes

Why “DESCRIBE” Doesn’t Work in SQL Server

Before we dive into the solutions. In MySQL or Oracle, the DESC command is a built-in shortcut to view table structure.

SQL Server does not support the DESCRIBE or DESC command.

Instead, Microsoft SQL Server utilizes a set of system stored procedures and ANSI-standard views to expose metadata. While this might seem less convenient initially, I have found that the SQL Server approach actually offers significantly more depth and granularity once you know how to use it.

Let’s look at the authoritative ways to retrieve this data.

Method 1: Using sp_help Stored Procedure

sp_help is a system stored procedure that provides the most comprehensive snapshot of a table.

It doesn’t just list columns; it gives you the entire context of the object.

The Syntax

Using it is straightforward. You execute the stored procedure followed by the table name.

SQL

EXEC sp_help 'SchemaName.TableName';

Note: While you can often skip the schema name if the table is in the default dbo schema, I always recommend including it (e.g., Sales.Orders) to avoid ambiguity, especially in large enterprise environments.

Decoding the Output

When you run this command, SQL Server doesn’t just return one table; it returns multiple result sets. Here is a breakdown of what I analyze when I look at the output:

  1. The Header: The first result gives you the table name, owner, type (user table), and creation date.
  2. The Columns: This is the equivalent of the DESC command. It lists:
    • Column_name
    • Type (Integer, Char, etc.)
    • Computed (Yes/No)
    • Length
    • Nullable
  3. The Identity: If you have an auto-incrementing ID (common in US-based e-commerce systems), this section tells you the “Seed” (starting number) and the “Increment” (how much it jumps).
  4. The Constraints: This is crucial. It lists all indexes, primary keys, and foreign keys associated with the table.

Example

EXEC sp_help 'dbo.Sales';

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

Describe Table in SQL Server

Use sp_help when you need a “deep dive” into a single table and need to see indexes and constraints alongside data types.

Method 2: Using INFORMATION_SCHEMA

Sometimes, I am writing a script that needs to be portable. I might be working on an application that needs to run on both SQL Server and PostgreSQL. In these cases, using Microsoft-specific stored procedures like sp_help is not ideal.

This is where INFORMATION_SCHEMA comes into play. These are ISO-standard views that exist within SQL Server to provide metadata.

The Query

To simulate a “describe” table command, you query the COLUMNS view.

SQL Syntax

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

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

Describe Table in SQL

Why I Use This Method

The power of this method lies in filtering.

Imagine you are working with a massive table in a legacy system—let’s call it US_Western_Region_Sales_Data—and it has 150 columns. You don’t want to scroll through the sp_help output. You only want to find columns related to “Tax”.

With INFORMATION_SCHEMA, you can filter:

SQL

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'US_Western_Region_Sales_Data'
AND COLUMN_NAME LIKE '%Tax%';

Key Takeaway: Use INFORMATION_SCHEMA when you need to programmatically access table definitions or when you need to filter the list of columns.

Method 3: The Shortcut (Alt + F1)

This is the perfect method. If you are using SSMS (SQL Server Management Studio), you do not need to type out full commands every time.

How to do it:

  1. Type the name of your table in the query window (e.g., Customers).
  2. Highlight the table name with your mouse or cursor.
  3. Press Alt + F1 on your keyboard.
describe table in sql server syntax

What happens?

SSMS automatically executes sp_help on the highlighted text and displays the results in the grid below. It is arguably the fastest way to check a schema while you are in the middle of writing a complex JOIN query.

Method 4: The System Views (sys.columns)

For the advanced users reading this, specifically those doing dynamic SQL generation or deep database documentation, the sys catalog views are the “bare metal” of SQL Server metadata.

Unlike INFORMATION_SCHEMA, which is a sanitized view for compatibility, sys.tables and sys.columns are the native internal representations of SQL Server objects.

The Query Structure

This method requires joining tables, as the data is normalized.

SQL

SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length,
    c.is_nullable
FROM
    sys.tables t
INNER JOIN
    sys.columns c ON t.object_id = c.object_id
INNER JOIN
    sys.types ty ON c.user_type_id = ty.user_type_id
WHERE
    t.name = 'Inventory';

When to use this?

I rarely use this for a quick check. I use this when I am building:

  • Code Generators: Scripts that automatically create C# classes or Python DataFrames based on SQL tables.
  • Auditing Tools: Checking for deprecated data types (like text or image) across the entire database.

Comparison: Which Method Should You Choose?

Featuresp_helpINFORMATION_SCHEMAAlt + F1sys.columns
Ease of UseHighMediumVery HighLow
Detail LevelExtreme (includes indexes)High (Standard properties)ExtremeCustomizable
PortabilitySQL Server OnlyCross-Database (Mostly)SSMS OnlySQL Server Only
Best ForDeep AnalysisScripts & filteringQuick ChecksMetadata Programming

Method 5: The “No-Code” GUI Approach

I know many developers prefer the command line, but we cannot ignore the Graphical User Interface. SQL Server Management Studio (SSMS) provides a visual way to describe a table without writing a single line of code.

The Steps

  1. Navigate to the Object Explorer on the left side of your screen.
  2. Drill down into your Database > Tables.
  3. Find your target table (e.g., dbo.Orders).
  4. Expand the standard “plus” (+) sign next to the table.
  5. Expand the Columns folder as shown in the screenshot below.
how to describe table in sql server using query

Here, you will see a list of columns with their data types and primary key icons listed right next to them.

Alternatively, you can right-click the table and select Design. This opens the table designer, showing you data types, default values, and description properties in a grid format. Check out the screenshot below for reference.

describe table sql

Warning: I advise caution when using the “Design” view in a production environment. If you accidentally change a data type and hit “Save,” you could trigger a massive transaction that locks the table. Use the “Design” view strictly for viewing, or stick to the Object Explorer tree.

Critical Data Points to Analyze

When you describe a table, you shouldn’t just glance at the column names. As a professional, here is the checklist I go through when I inspect a new table definition.

1. Precision and Scale

If you see a column defined as DECIMAL, don’t just move on. Look at the numbers in the brackets, like DECIMAL(18, 2).

  • Precision (18): The total number of digits.
  • Scale (2): The number of digits after the decimal point. In US financial applications, relying on standard FLOAT can lead to rounding errors. Ensuring the table uses the correct DECIMAL precision is vital.

2. Character Sets (VARCHAR vs. NVARCHAR)

This is a common “gotcha.”

  • VARCHAR uses 1 byte per character.
  • NVARCHAR uses 2 bytes per character and supports Unicode.

If your application needs to support international names or special characters, seeing VARCHAR in the table description is a red flag. I always check this to ensure future scalability.

3. Nullability

This determines data integrity. If a column is nullable, your code must handle potential crashes when data is missing. I always look for the IS_NULLABLE flag to decide if I need to wrap my queries in ISNULL() or COALESCE() functions.

Conclusion

“Describing” a table in SQL Server is more than just finding out column names; it is about understanding the DNA of your data structure. While the lack of a simple DESCRIBE command might seem like a hurdle initially, the alternatives provided by T-SQL offer a robust toolkit for any data professional.

Whether you prefer the comprehensive output of sp_help, the quick-fire speed of the Alt+F1 shortcut, or the programmatic flexibility of sys.columns, mastering these commands is essential.

You may also like the following articles.