Adding column if not exist with value

• jerome.morris

  SSCrazy

  Points: 2829

  More actions

  September 18, 2013 at 3:15 pm

  #283425

  Hi all, using the below, how do I set the DBVersion to 1 with this line, if the column DBVersion exist I don't want to change the number but if it doesn't exist I want to create it and have DBVersion set to 1

  if not exists (select * from syscolumns where id=object_id('ConfigTB') and name='DBVersion') alter table ConfigTB add DBVersion INT

  Thanks

• jerome.morris

  SSCrazy

  Points: 2829

  More actions

  September 18, 2013 at 3:51 pm

  #1651316

  if not exists

  (select * from syscolumns where id=object_id('ConfigTB') and name='DBVersion')

  alter table ConfigTB add DBVersion int DEFAULT '1' NOT NULL

  Any problem with this method?

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  September 18, 2013 at 4:15 pm

  #1651321

  First, you should use sys.columns, not syscolumns. Next you should name your constraints:

  if not exists (select * from sys.columns where object_id=object_id('ConfigTB') and name='DBVersion')

  alter table ConfigTB add DBVersion int CONSTRAINT def_DBVersion DEFAULT 1 NOT NULL

  Also, observe that since this is an int column the default value should not be in quotes, because it is not a string.

  If you don't want this constraint once the column has been added, just drop the constraint. Which is easy when you named it.

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]