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 arecouple 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.columnsWHERE 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.columnsWHERE 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 NULLBEGIN
PRINT 'Your Column Exists'END

For AdventureWorks sample database

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

Method 3

IF EXISTS(SELECT TOP 1 *FROM INFORMATION_SCHEMA.COLUMNSWHERE [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.COLUMNSWHERE [TABLE_NAME] = 'Department'AND [COLUMN_NAME] = 'Name'AND [TABLE_SCHEMA] = 'HumanResources')BEGIN
PRINT 'Your Column Exists'END

Let me know if you know any other method to find if Column Exists in SQL Server Table.

Thanks to Pinal Dave.