September 18, 2013 at 3:15 pm
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
September 18, 2013 at 3:51 pm
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?
September 18, 2013 at 4:15 pm
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]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy