Blog Archives

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: