Blog Archives

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’

SQL Server 2008 Prevent Saving Changes Setting

SQL Server 2008 has a setting that PREVENTS saving certain table schema changes (such as changing column order) – and this setting is ENABLED by default.

When I first encountered the effects of this setting, I was attempting to rearrange the column order of a table. Using the GUI, I went to the table design view and moved one column in front of another.

When I hit ‘Save’, I received this message:

When a table’s schema is changed, the process that SQL Server performs drops the table and recreates it. The new setting that prevents this is called ‘Prevent saving changes that require table re-creation‘, and it’s easy enough to turn off. I’m sure it has been put in place as a safeguard to prevent unintended table changes. To turn off the setting, go to (on the menu bar) Tools/Options/Designers/Table and Database Designers, then under the Table Options section, uncheck Prevent saving changes….

Schema changes will not be blocked.  You’re welcome.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: