Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Sql Server Display Column Name Using COL_NAME

In SQl Server Metadata Catalog we often see the use of the column named column_id. Usually it is not meaningful for us and we would prefer to see a real column name. Obviously we can always perform a join with sys.columns table and retrieve the column name directly, however quite often that join would only add unneeded complexity to our query. In such situations COL_NAME functions becomes very handy.

COL_NAME returns column name, using object_id, which is id of the view or table and column_id. Here is small example, for start let’s assume that we do know the table_id. (Remark: The following queries are run on sample database AdventureWorks2008). In below examples I assumed that we query column with column_id equal 1.

select object_id, name from sys.tables where name = 'Address' 
object_id    name
----------- -----------
85575343    Address

select COL_NAME(85575343, 1) as column_name
column_name
---------------
AddressID

If we do not know object_id, but we know object_name, we can use OBJECT_ID function as in the below example:

select COL_NAME(object_id('Person.Address'),1) as column_name 
column_name
---------------
AddressID