SQL SERVER – Find Out Column Name Using COL_NAME() Function

In SQL Server, there are many methods to find out the column names in a table. If you want to find out the column names based on the ordinal position you can do it querying system view size. Columns. Do you know that you can also do this by using COL_NAME() system function?

SQL SERVER - Find Out Column Name Using COL_NAME() Function columnview1-800x226

Let us create this simple table

USE tempdb
CREATE TABLE #testing(id INT, names VARCHAR(100));

Now to find out the second column, you can use the following code

SELECT
	name AS col_name FROM sys.columns 
WHERE 
	object_id=object_id('tempdb..#testing') AND column_id=2

The result is

col_name
—————
names

The above returns the second column. If you want to know the first column, you can apply the filter column_id=1

Similarly the same can be achieved using the simple system function COL_NAME()

COL_NAME accepts two parameters table id and the ordinal position and returns the column name

SELECT COL_NAME(object_id('tempdb..#testing'),2) AS col_name

The result is

col_name
—————
names

Similarly, if you want to find out the first column just use

SELECT COL_NAME(object_id('tempdb..#testing'),1) AS col_name

This is really simple function that is rarely used. Let me know your thought about this blog post. I am very eager to know if you knew about this function or not.

Have you ever used this function in your business?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – How to Check if a Column Exists in SQL Server Table?
Next Post
SQL SERVER – How to Convert CollationID to Collation Name?

Related Posts

2 Comments. Leave new

  • Without Ordinal position we cannot find out if a table exists or not. Maybe that is the reason, why its rarely used.

    Reply
  • SAJJAN SARKAR
    July 31, 2017 7:40 pm

    Not used it, didnt know about it, cant see a real need for it.

    Reply

Leave a Reply

Menu