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’

About these ads

Posted on February 10, 2012, in SQL Server and tagged , , , , , , , , , . Bookmark the permalink. 2 Comments.

  1. Seth, why do you use the system tables and not the information_schema views? I thought best practices were to use the information_schema detaisl as much as possible due to potentail changes to the system tables and objects?

    • Thanks Mark – You have a good point – I like this better:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: