Wednesday, July 24, 2013

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'NameAND 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 *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 *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.
Post a Comment

Find a cool free stuff everyday

Giveaway of the Day

Hiren Bharadwa's Posts

DotNetJalps