Sql Server: Check If Column Exists In Table

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. Here I’ve listed down few of the methods to check for a column in a table or multiple tables in the database.

Check If Column Exists In A Table

There are multiple methods to check for the 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'

Check If Column Exists In SQL Server Table 01

(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'

Check If Column Exists In SQL Server Table 02

(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')

Check If Column Exists In SQL Server Table 03

(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')

Check If Column Exists In SQL Server Table 04

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'

Check If Column Exists In SQL Server Table 05

(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 Column Exists In SQL Server Table 06

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%' 

SQL Find Stored procedure Containing Text

(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%'

SQL Find Stored procedure Containing Text

Reference

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

 

Leave your thoughts...