Get List of Columns in a Table

There is no way to write a SQL Server SELECT statement, using the wildcard, to select all columns in a table except for any number of particular columns.  

For example, if you wanted to return all fields from the Person.Address table except for the City field, you cannot write a query like this:

SELECT ( * -City ) FROM Person.Address

If you need to select 99 of 100 columns in a table, you have to list all 99.  This can be a painful task.  For now, the best way to accomplish this is to generate a list of field names from the table, and copy them into your query.  Here’s how to do it.

Every database in SQL Server 2008 contains system views named sys.obects and sys.columns.  Sys.objects contains all database objects: system tables, user tables, constraints, views, etc.  Sys.columns contains all columns from every table, along with the same object_id of it’s parent object (the table).  By joining these system tables together, you can get a quick list of columns that you can copy and paste into your query:

USE AdventureWorks
GO
SELECT sc.name ColumnName
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
WHERE so.name = 'Contact'

This is certainly one query to memorize!

To list columns from ANY user table, drop the table specification, add a filter on sys.objects for ‘type’, and include the table name by using the OBJECT_NAME function:

USE AdventureWorks
GO
SELECT OBJECT_NAME(sc.OBJECT_ID) TableName, sc.name ColumnName
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
WHERE so.type = 'U'

This query can be taken even further by joining to the sys.types view, where data type metadata is stored:

USE AdventureWorks
GO
SELECT OBJECT_NAME(sc.OBJECT_ID) TableName, sc.name ColumnName,
t.name [DataType], sc.is_nullable Nullable, sc.is_identity Is_identity
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
JOIN sys.types t
ON sc.user_type_id=t.user_type_id
WHERE so.type = 'U'

EDIT:

One reader brought up an alternative method to get a list of columns in a table, by using one of the information schema views.  I like his idea better than using sys.columns:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = ‘AdventureWorks’
AND TABLE_NAME = ‘Contact’