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