Introduction

In SQL Server, column names are available from the system catalog and from standard metadata views. The best query depends on whether you want a portable standards-style answer, a SQL Server-specific answer with more detail, or a quick interactive lookup.

Use INFORMATION_SCHEMA.COLUMNS for a Standard Query

The simplest portable approach is INFORMATION_SCHEMA.COLUMNS.

1SELECT COLUMN_NAME
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE TABLE_SCHEMA = 'dbo'
4  AND TABLE_NAME = 'Customers'
5ORDER BY ORDINAL_POSITION;

This is a good default when you want readable metadata access and do not need many SQL Server-specific features.

Two important filters are:

• 'TABLE_SCHEMA, because table names are not unique across schemas'
• 'TABLE_NAME, because the view contains every table in the database'

Ordering by ORDINAL_POSITION gives the columns in table order.

Use sys.columns for SQL Server-Specific Metadata

If you want the SQL Server catalog directly, use sys.columns with OBJECT_ID.

1SELECT c.name AS column_name
2FROM sys.columns AS c
3WHERE c.object_id = OBJECT_ID('dbo.Customers')
4ORDER BY c.column_id;

This is often more useful than INFORMATION_SCHEMA when you want to join into other SQL Server catalog views later.

For example, the same family of catalog views can also tell you about data types, defaults, computed columns, and identity properties.

Include More Metadata When Needed

Often you do not want only the column names. You want names plus types and nullability.

1SELECT
2    c.name AS column_name,
3    t.name AS data_type,
4    c.max_length,
5    c.is_nullable
6FROM sys.columns AS c
7JOIN sys.types AS t
8    ON c.user_type_id = t.user_type_id
9WHERE c.object_id = OBJECT_ID('dbo.Customers')
10ORDER BY c.column_id;

This is a better query for tooling, migrations, or documentation generation because it gives enough detail to be useful beyond a simple list.

sp_columns Works for Interactive Inspection

SQL Server also provides a stored procedure for metadata lookup.

EXEC sp_columns @table_name = 'Customers', @table_owner = 'dbo';

This is fine for quick manual inspection in SSMS, but for application code or repeatable scripts, direct queries against the metadata views are usually cleaner and easier to control.

Build a Reusable Query for Any Table

If the table name needs to be parameterized, use a variable and schema-qualified name.

1DECLARE @schema_name sysname = 'dbo';
2DECLARE @table_name sysname = 'Customers';
3
4SELECT c.name AS column_name
5FROM sys.columns AS c
6WHERE c.object_id = OBJECT_ID(QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name))
7ORDER BY c.column_id;

This keeps the lookup precise and avoids relying on ambiguous table names.

When Dynamic SQL or Tooling Needs This

Common use cases include:

• generating exports
• building dynamic insert or select statements
• creating documentation
• verifying schema drift in deployment tools
• auditing tables for expected columns

In these cases, sys.columns is often the better foundation because it integrates naturally with the rest of SQL Server's system catalog.

INFORMATION_SCHEMA Versus sys.*

A useful rule is:

• choose INFORMATION_SCHEMA when you want a simple standards-style answer
• choose sys.columns when you want SQL Server-native detail or richer joins

Neither is universally “more correct.” They are different tools for slightly different needs.

Permissions and Context Matter

Metadata visibility depends on permissions. If a query returns less than expected, it may not mean the table is missing. It may mean the current login cannot see that object's metadata fully.

Also remember that the query runs in the current database context. If you are pointed at the wrong database, even a perfectly written metadata query returns the wrong answer.

Common Pitfalls

A common mistake is filtering only by table name and ignoring schema. That breaks in databases where the same table name exists in multiple schemas.

Another mistake is assuming INFORMATION_SCHEMA exposes every SQL Server-specific detail you might need. It often does not.

Developers also sometimes forget to order by position, which makes the result harder to compare with the real table definition.

Finally, do not assume an empty result means the table definitely does not exist. Check database context and permissions too.

Summary

• Use INFORMATION_SCHEMA.COLUMNS for a simple standards-style column list.
• Use sys.columns when you want SQL Server-native metadata and richer detail.
• Filter by both schema and table name.
• Order by ordinal or column ID if you want real table order.
• For application and tooling scenarios, metadata queries are usually better than manual SSMS inspection.