COL_NAME function to know the column name

In SQL Server, there are several methods to get the column names of a table. You can use system views like sys.columns, INFORMATION_SCHEMA.COLUMNS, etc. However did you know that there is a function named COL_NAME that accepts the object id of a table and column number and returns the column name?

Consider the following table

use tempdb
create table test(id int, name varchar(100), dob datetime)

If you want to know the first column name of the table, you can use the following code

select col_name(object_id('test'),1) as column_name;

The result is

column_name
--------------
id

Similarly you can get the other column names by using

select col_name(object_id('test'),2) as column_name;
select col_name(object_id('test'),3) as solumn_name;

You get NULL if there is an error or if you do not have permission to view the object

Have you ever used this function? I do not see practical usage of this function but it may avoid querying on the  system views.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s