SQL Query to Get Column Names from Table in SQL Server

In this article, I am going to walk you through exactly how to write a SQL query to get column names from a table in SQL Server. I will cover the standard ANSI methods, the SQL Server-specific system views, and the quick shortcuts I use daily.

SQL Query to Get Column Names from Table in SQL Server

Why Do You Need to Query Column Names?

Before we dive into the code, let’s establish why we are doing this.

Dynamic SQL Generation: You are writing a script that needs to build an INSERT statement dynamically based on whatever columns exist in the target table.
Schema Auditing: You need to find every column named EmailAddress across 500 tables to apply a new data masking policy.
Documentation Automation: You are building a data dictionary and need to pull column names, data types, and max lengths into a readable report.
Validation: You need to check if a specific column exists before running an ALTER TABLE script to avoid errors.

Regardless of the use case, SQL Server gives us three primary ways to do this.

Method 1: The Standard Way (`INFORMATION_SCHEMA.COLUMNS`)

If you want your code to be portable—meaning it could theoretically run on PostgreSQL or MySQL with minimal changes—you should always use the INFORMATION_SCHEMA views. These are ISO standard views that SQL Server maintains to ensure compliance.

This is my go-to method for 90% of tasks because it is readable and self-explanatory.

The Basic Syntax

To get the column names for a specific table, you query the INFORMATION_SCHEMA.COLUMNS view.

SQL

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

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

SQL Query to Get Column Names from Table in SQL Server

Understanding the Output

When you run this, you get a clean list. Here is what the critical columns mean:

COLUMN_NAME: The actual name of the attribute.
ORDINAL_POSITION: The order in which the column appears in the table (1, 2, 3…). This is crucial if you are generating INSERT statements and need to match the order.
DATA_TYPE: The raw type (e.g., varchar, int, datetime).
IS_NULLABLE: Tells you if the column accepts NULL values (YES or NO).

Why I Prefer This Method

I prefer INFORMATION_SCHEMA because it abstracts away the internal object IDs. You don’t need to know the object_id of the table; you just need its name. It is clean, it is standard, and it is easy to memorize.

Method 2: The “Internal” Way (`sys.columns`)

While INFORMATION_SCHEMA is great for general use, it sometimes lacks the deep, SQL Server-specific metadata that a Database Administrator (DBA) needs. For example, if you need to know if a column is an IDENTITY column or a COMPUTED column, the standard view won’t tell you.

For that, we go to the source: the System Catalog Views.

The Query

This approach requires joining two system views: sys.columns and sys.tables (or sys.objects).

SQL

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

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

When to Use This

I use this method when I am doing heavy-duty schema work.

Identity Checks: The is_identity flag is vital if you are replicating data and need to turn IDENTITY_INSERT on or off.
Computed Columns: You can check is_computed to see if a column is actually a formula.
User-Defined Types: If your database uses custom alias types, sys.types handles them more accurately than the information schema.

Method 3: The Lazy Shortcut (`sp_help`)

Sometimes, I am not writing a script. I am just in the middle of a debugging session, and I need to see what columns are in the Orders table right now. I don’t want to type a full SELECT statement.

In these cases, I use the built-in stored procedure sp_help.

The Syntax

SQL

EXEC sp_help 'dbo.Employees';

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

Or simply highlight the table name in SSMS and press Alt+F1.

The Output

This returns multiple result sets.

Table Info: Creation date, owner.
Column Info: A table listing every column, its type, length, and collation.
Identity: Which column is the identity.
Indexes: A list of all keys and indexes on the table.
Constraints: A list of foreign keys and checks.

Advanced Scenario: Getting a Comma-Separated List of Columns

Here is a scenario I face constantly. You need to write a SELECT statement, but the table has 50 columns. You do not want to type them all out, but you also know that using SELECT * is bad practice (it kills performance and breaks if the schema changes).

You want a comma-separated string of all columns: Column1, Column2, Column3...

We can use the STRING_AGG function (available in SQL Server 2017 and later) combined with our sys.columns query to generate this instantly.

The Generator Query

SQL

SELECT 
    STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY column_id) AS ColumnList
FROM 
    sys.columns
WHERE 
    object_id = OBJECT_ID('dbo.Employees');

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

I keep this snippet in my “snippets manager” in SSMS. It saves me hours of typing every year. If you are on an older version of SQL Server (pre-2017), you have to use the FOR XML PATH trick, but let’s assume you are on a modern stack.

Comparison Table: Which Method Should You Use?

To help you decide which query to copy-paste, I have broken down the pros and cons of each approach.

Feature	INFORMATION_SCHEMA	sys.columns	sp_help
Portability	High (ANSI Standard)	Low (SQL Server only)	None (SQL Server only)
Speed	Fast	Fastest	Slower (runs multiple queries)
Detail Level	Basic (Name, Type, Order)	Deep (Identity, Computed, FileStream)	Comprehensive (Includes Indexes/Keys)
Use Case	Reports, Generic Scripts	DB Admin, Dynamic SQL	Ad-hoc checks by humans
Readability	High	Medium (Requires joins)	N/A (Procedure call)

Handling Special Cases

1. What About Views?

The INFORMATION_SCHEMA.COLUMNS view is smart. It includes columns from Tables and Views. If you want to filter strictly for tables, you need to join it with INFORMATION_SCHEMA.TABLES.

SQL

SELECT 
    C.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS C
INNER JOIN 
    INFORMATION_SCHEMA.TABLES T 
    ON C.TABLE_NAME = T.TABLE_NAME 
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE 
    T.TABLE_TYPE = 'BASE TABLE' -- Filters out Views
    AND T.TABLE_NAME = 'Employees';

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

2. Searching the Entire Database

Sometimes you don’t know the table name. You just know there is a column named ZipCode somewhere in the database, and you need to find it.

This is where the power of SQL querying shines over the GUI.

SQL

SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name LIKE '%ZipCode%';

I use this specific query during database discovery phases. If a client asks, “Where do we store customer phone numbers?”, running this search for %Phone% usually reveals the answer in milliseconds.

Common Pitfalls to Avoid

As you start writing these queries, keep these “pitfalls” in mind.

Relying on `SELECT *` in Production

I mentioned this earlier, but it bears repeating. Do not use column discovery to justify using SELECT *. Always explicitly list your columns. Using the STRING_AGG trick I showed above removes the excuse of “it takes too long to type.”

Ignoring Permissions

Just because you can query a table doesn’t mean you can query the system views. However, in SQL Server, metadata visibility is generally secure by default. If you do not have permission to SELECT from the Employees table, the system views (sys.columns) will usually hide those rows from you. You won’t see an error; you just won’t see the columns. This is a security feature called Metadata Visibility Configuration.

The `sp_columns` Legacy

You might see some older tutorials referencing sp_columns. This is an old ODBC stored procedure.

SQL

EXEC sp_columns 'Employees';

While it works, I recommend avoiding it. It returns a result set that is formatted for ODBC drivers (like returning int as 4), which is rarely what a modern T-SQL developer wants. Stick to INFORMATION_SCHEMA or sys.columns.

Conclusion

Retrieving column names in SQL Server is a fundamental skill that bridges the gap between a casual user and a Data Professional.

My Final Recommendation:

Use INFORMATION_SCHEMA.COLUMNS for general reporting and application code. It is clean, easy to read, and future-proof.
Use sys.columns When you are doing deep database administration tasks like checking for identity columns or computed fields.
Use sp_help (Alt+F1) When you are just looking around manually.

You may also like the following articles:

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.

SQL Query to Get Column Names from Table in SQL Server

Why Do You Need to Query Column Names?

Method 1: The Standard Way (INFORMATION_SCHEMA.COLUMNS)

The Basic Syntax

Understanding the Output

Why I Prefer This Method

Method 2: The “Internal” Way (sys.columns)

The Query

When to Use This

Method 3: The Lazy Shortcut (sp_help)

The Syntax

The Output

Advanced Scenario: Getting a Comma-Separated List of Columns

The Generator Query

Comparison Table: Which Method Should You Use?

Handling Special Cases

1. What About Views?

2. Searching the Entire Database

Common Pitfalls to Avoid

Relying on SELECT * in Production

Ignoring Permissions

The sp_columns Legacy

Conclusion

Method 1: The Standard Way (`INFORMATION_SCHEMA.COLUMNS`)

Method 2: The “Internal” Way (`sys.columns`)

Method 3: The Lazy Shortcut (`sp_help`)

Relying on `SELECT *` in Production

The `sp_columns` Legacy