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?

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)