Check If Column Exists In Table Of SQL Server

As SQL Server developers, we often needs to check if column exists in a specific table or any table in the database. We even may need to list down the tables in the database having a specific column. So, how to check if column exists in SQL Server database? Here, I’ve listed down few of the methods to check for a column in a table or multiple tables in the database. This article is divided into three major sections. Jump to your desired section:

• Check If Column Exists In A Table Jump To Topic ↓
• List Of Tables Having The Column Jump To Topic ↓
• Check If Any Stored Procedure Uses The Column Jump To Topic ↓

Check If Column Exists In A Table

There are multiple methods to check for existence of the column in a table. Let’s see them one by one.

(i) Using INFORMATION_SCHEMA.COLUMNS

The easiest and straightforward way to check for the column in a table is to use the information schema for column system view. Wright a select query for INFORMATION_SCHEMA.COLUMNS as shown below. If the query returns record, then the column is available in the table.

Query Template

USE {{Database Name}}
SELECT * 
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = '{{Table Name}}' 
	AND COLUMN_NAME = '{{Column Name}}'

Example

USE WideWorldImporters
SELECT * 
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'CustomerTransactions' 
	AND COLUMN_NAME = 'CustomerID'

(ii) Using SYS.COLUMNS

Instead of using the information schema view, you can directly use the SYS.COLUMNS system table to check if column exists in a table. If the query returns record, then the column is available in the table.

Query Template

USE {{Database Name}}
SELECT * 
	FROM SYS.COLUMNS
	WHERE OBJECT_ID = OBJECT_ID('{{Table Name}}')
	AND NAME = '{{Column Name}}'

Example

USE WideWorldImporters
SELECT * 
	FROM SYS.COLUMNS
	WHERE OBJECT_ID = OBJECT_ID('Sales.CustomerTransactions')
	AND NAME = 'CustomerID'

(iii) Using COL_LENGTH

Another method to find if the column exists in a table is by using COL_LENGTH system function. This function returns the length of the column if it exists in the table. If not, it will return NULL.

Query Template

USE {{Database Name}}
SELECT COL_LENGTH('{{Table Name}}', '{{Column Name}}')

Example

USE WideWorldImporters
SELECT COL_LENGTH('Sales.CustomerTransactions', 'CustomerID')

(iv) Using COLUMNPROPERTY

Yet another method is by using COLUMNPROPRTY system metadata function. This function returns the value of the column’s property if it exists in the table. If not, it will return NULL.

Query Template

USE {{Database Name}}
SELECT COLUMNPROPERTY(OBJECT_ID('{{Table Name}}'), '{{Column Name}}', 'ColumnId')

Example

USE WideWorldImporters
SELECT COLUMNPROPERTY(OBJECT_ID('Sales.CustomerTransactions'), 'CustomerID', 'ColumnId')

List Of Tables Having The Column

So far, we have seen the methods to check if column exists in a specific table in the database. Now we will see the methods to find all the tables in the database having the column. This is useful to find out the tables having a column as foreign key.

(i) Using INFORMATION_SCHEMA.COLUMNS

Query Template

USE {{Database Name}}
SELECT 
	TABLE_SCHEMA, TABLE_NAME
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE COLUMN_NAME = '{{Column Name}}'

Example

USE WideWorldImporters
SELECT 
	TABLE_SCHEMA, TABLE_NAME
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE COLUMN_NAME = 'CustomerID'

(ii) Using SYS.COLUMNS

Query Template

USE {{Database Name}}
SELECT OBJECT_NAME(object_id) AS 'Table Name', name as 'Column Name'
	FROM SYS.COLUMNS
	WHERE NAME = '{{Column Name}}'

Example

USE WideWorldImporters
SELECT OBJECT_NAME(object_id) AS 'Table Name', name as 'Column Name'
	FROM SYS.COLUMNS
	WHERE NAME = 'CustomerID'

Check If Stored Procedure Uses The Column

To check and find out the SQL stored procedures using a particular column, you have to use the technique of “search for stored procedures containing a text”

(i) Using INFORMATION_SCHEMA.ROUTINES

Query Template

SELECT 
	ROUTINE_NAME, 
	ROUTINE_SCHEMA
FROM 
	INFORMATION_SCHEMA.ROUTINES 
WHERE 
	ROUTINE_TYPE='PROCEDURE' AND
	ROUTINE_DEFINITION LIKE '%{{Column Name}}%' 

Example

SELECT 
	ROUTINE_NAME, 
	ROUTINE_SCHEMA
FROM 
	INFORMATION_SCHEMA.ROUTINES 
WHERE 
	ROUTINE_TYPE='PROCEDURE' AND
	ROUTINE_DEFINITION LIKE '%Employee%' 

(ii) Using SYS.PROCEDURES

Query Template

SELECT
    name,
    OBJECT_DEFINITION(object_id)
FROM
    SYS.PROCEDURES 
WHERE
    OBJECT_DEFINITION(object_id) LIKE '%{{Column Name}}%'

Example

SELECT
    name,
    OBJECT_DEFINITION(object_id)
FROM
    SYS.PROCEDURES 
WHERE
    OBJECT_DEFINITION(object_id) LIKE '%Employee%'

Reference

• About information schema views at MSDN.
• About COL_LENGTH at MSDN.