SQL SERVER – Check If Column Exists in SQL Server Table

A very frequent task among SQL developers is to check if any specific column exists in the database table or not. Based on the output developers perform various tasks. Here are couple of simple tricks which you can use to check if column exists in your database table or not.

Method 1

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'Name' AND OBJECT_ID = OBJECT_ID(N'[HumanResources].[Department]'))
BEGIN
PRINT 'Your Column Exists'
END

Method 2

IF COL_LENGTH('table_name','column_name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF COL_LENGTH('[HumanResources].[Department]','Name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END

Method 3

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'TableName'
AND [COLUMN_NAME] = 'ColumnName'
AND [TABLE_SCHEMA] = 'SchemaName')
BEGIN
PRINT 'Your Column Exists'
END

For AdventureWorks sample database

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'Department'
AND [COLUMN_NAME] = 'Name'
AND [TABLE_SCHEMA] = 'HumanResources')
BEGIN
PRINT 'Your Column Exists'
END