Cry How to...
Add a column to a table unless it already exists
To add a column to a SQL Server database table, checking first that the column does not already exist:
if not exists (select * from syscolumns
where id=object_id('<table_name>') and name='<column_name>')
alter table <table_name> add <column_name> <column_definition>
where: <table_name> is the name of the table, <column_name> is the name of the column and <column_definition> is the definition of the column used when creating the column.
For example:
if not exists (select * from syscolumns
where id=object_id('Employees') and name='MaidenName')
alter table Employees add MaidenName varchar(64) NULL
go
These notes have been tested against SQL Server 7, SQL Server 2000, SQL Server 2005 and SQL Server 2008.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.