In SQL Server, you can use the COL_NAME() function to return a column’s name, based on its ID and its parent table ID.

This can be useful when querying a table that only stores the column ID and the parent table’s ID.

The COL_NAME() function requires two arguments: the table ID, and the column ID.

Syntax

The syntax goes like this:

COL_NAME ( table_id , column_id )

Example 1 – Basic Usage

Here’s a basic example to demonstrate how it works.

SELECT COL_NAME(885578193, 1) AS Result;

Result:

+----------+
| Result   |
|----------|
| ArtistId |
+----------+

In this case, I returned the name of column 1 from the table with an ID of 885578193.

Example 2 – Get the Table ID

If you only know the name of the table, you can use OBJECT_ID() to return its ID based on the table name.

I happen to know the name of the above table, so I could change the previous example to this:

SELECT COL_NAME(OBJECT_ID('dbo.Artists'), 1) AS Result;

Result:

+----------+
| Result   |
|----------|
| ArtistId |
+----------+

Here it is again, but with the table ID included:

SELECT 
  OBJECT_ID('dbo.Artists') AS [Table ID],
  COL_NAME(OBJECT_ID('dbo.Artists'), 1) AS [Column Name];

Result:

+------------+---------------+
| Table ID   | Column Name   |
|------------+---------------|
| 885578193  | ArtistId      |
+------------+---------------+

Example 3 – More Columns

Here, I add a few more columns to the output.

SELECT 
  COL_NAME(OBJECT_ID('dbo.Artists'), 1) AS [Column 1],
  COL_NAME(OBJECT_ID('dbo.Artists'), 2) AS [Column 2],
  COL_NAME(OBJECT_ID('dbo.Artists'), 3) AS [Column 3],
  COL_NAME(OBJECT_ID('dbo.Artists'), 4) AS [Column 4];

Result:

+------------+------------+------------+------------+
| Column 1   | Column 2   | Column 3   | Column 4   |
|------------+------------+------------+------------|
| ArtistId   | ArtistName | ActiveFrom | CountryId  |
+------------+------------+------------+------------+

Example 4 – Checking Dependencies

Here’s an example where I use COL_NAME() in a query that checks the sys.sql_expression_dependencies system view for dependency information. This view returns the column IDs rather than their names, so I use COL_NAME() to get their names.

SELECT
    OBJECT_NAME(referencing_id) AS [Referencing Entity],
    referenced_minor_id,
    COL_NAME(referenced_id, referenced_minor_id) AS [Column]
FROM sys.sql_expression_dependencies;

Result:

+----------------------+-----------------------+------------+
| Referencing Entity   | referenced_minor_id   | Column     |
|----------------------+-----------------------+------------|
| uspGetClient         | 0                     | NULL       |
| uspGetClient         | 0                     | NULL       |
| uspGetOrdersByClient | 0                     | NULL       |
| uspGetOrdersByClient | 0                     | NULL       |
| uspGetOrdersByClient | 0                     | NULL       |
| uspGetAlbumsByArtist | 0                     | NULL       |
| chkClientCode        | 1                     | ClientCode |
+----------------------+-----------------------+------------+

Example 5 – In a WHERE Clause

Here, I use the COL_NAME() function in a WHERE clause so that rows with a referenced_minor_id of NULL are not returned.

SELECT
    OBJECT_NAME(referencing_id) AS [Referencing Entity],
    referenced_minor_id,
    COL_NAME(referenced_id, referenced_minor_id) AS [Column]
FROM sys.sql_expression_dependencies
WHERE COL_NAME(referenced_id, referenced_minor_id) IS NOT NULL;

Result:

+----------------------+-----------------------+------------+
| Referencing Entity   | referenced_minor_id   | Column     |
|----------------------+-----------------------+------------|
| chkClientCode        | 1                     | ClientCode |
+----------------------+-----------------------+------------+