Blog Archives
Get List of Columns in a Table
Posted by Seth
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’