troubleshooting Question

Need help with default constraint...

Avatar of WebAppDeveloper
WebAppDeveloperFlag for United States of America asked on
Microsoft SQL Server 2005
3 Comments1 Solution230 ViewsLast Modified:
Hi,

I have the following script that has already been run in certain databases. For some reason, my dba did not run this script in the production environment, and instead, he added the  SequenceNumber column manually. I want to write a script (to be run in production) to check if the SequenceNumber (that was manually added by the dba) has a default constraint with default of 0. If it does not, then add "CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES".  In other words, I want to make sure that whenever a new record goes into the 'tblProducts' table in production, a default value of 0 is inserted into the SequenceNumber column.  If you can provide SQL syntax for such script, that will be great.

Thanks.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'tblProducts' AND COLUMN_NAME = 'SequenceNumber')
BEGIN
   ALTER TABLE dbo.tblProducts ADD SequenceNumber INT NULL CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES
END
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros