SQL SERVER – Check If Column Exists in SQL Server Table
- by Pinal Dave
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
Let me know if you know any other method to find if Column Exists in SQL Server Table.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
