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 and SQL Server 2000.