original article: http://www.cryer.co.uk/brian/sqlserver/howtoaddcolumnunlessexists.htm

 

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('INS_CORP_POLICY') and name='PROCESS_BUSINESS_UNIT_ID')

begin

    alter table INS_CORP_POLICY add PROCESS_BUSINESS_UNIT_ID int NULL

              print 'column added'

end

else

begin

              print 'column not added'

end

 

go