The W Blog

SQL Basics - Finding a given column

I have decided to step away from the SQL 2008 CTP for a bit and post a couple items that keep coming up.  I have been spending more time in the MSDN Transact SQL forums and have noticed a consistent stream of questions around some basic operations with transact SQL.  After a conversation with a client this week that touched on the same subject  I thought "I smell a blog series".  :)

So I have decided to post a blog a day this week on what I'm calling "SQL Basics".  I will be covering the following items:

• Finding a given column by name
• Finding tables based on a certain criteria (i.e. name, containing certain data types, etc.)
• Finding identity columns
• String concatenation tricks

So with those topics in mind, let's get rolling.  Today I will show you four ways to find a given column in your database by name.

Option 1 uses the sys.columns catalog view.  This view returns a row for each column of an object that has columns, these include:

• Table valued assembly functions
• Inline table-valued functions
• Internal tables
• System tables
• Table-valued SQL functions
• User tables
• Views

The syntax is:

--Option 1
SELECT OBJECT_NAME(object_id) AS objectName
    ,name AS columnName
FROM sys.columns
WHERE name = 'ProductID';

Option 2 uses the sys.syscolumns compatibility view.  This view returns a row for every column in every table and view, as well as a row for each parameter in a stored procedure in the database.  This view is created for backward compatibility, so its future is not guaranteed.

The syntax is:

--Option 2
SELECT OBJECT_NAME(id) AS objectName
    ,name AS columnName
FROM sys.syscolumns
WHERE name = 'ProductID';

Option 3 utilizes the sys.all_columns object catalog view.  This view shows the columns of all user-defined and system objects.

The syntax is:

--Option 3
SELECT OBJECT_NAME(object_id) AS objectName
    ,name AS columnName
FROM sys.all_columns AS ac
WHERE name = 'ProductID';

Option 4 uses the the INFORMATION_SCHEMA.COLUMNS view.  This view returns one row for each column that can be accessed by the current user in the current database.

The syntax is:

--Option 4
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ProductID';

So there are four ways to get around in meta data when you need to do a column search.  Take your time to expand the queries provided and notice the wealth of information provided to you in the various views.  Happy hunting!