Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’
End
Else
Begin
–Column already exists. leave the table as it is.
Print ‘Column already exists.’
End
Go

Drop table testTable
Go