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.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblProducts' AND COLUMN_NAME = 'SequenceNumber')
ALTER TABLE dbo.tblProducts ADD SequenceNumber INT NULL CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES