add column default using TSQL to existing column
-
I've search for syntax and am stumped. I'm almost positive I've done this in the past but am at a loss to find the proper syntax now.
I want to add a default value to a column that already exist. I do not want to recreate the column or the table. I want to name the default so that SQL will not create it with a random number tacked on. This is so that SQL Compare tools will not hit on constraint names as different between alpha, beta, and production versions of the databases. I will have to do this hundreds of times for multiple columns and wanted a TSQL script. The syntax I keep playing with is something like:
Alter table dbo.ABC_audit
alter column audit_timestmp datetime null CONSTRAINT [DF_ABC_audit_timestmp] DEFAULT (getdate())
Of course this does not work. I do not need to change the column definitions (ie the column datatype and nullablility are OK).
-
Just go in EM, add a default constraint on an existing column and see what code is generated.. it's gonna look something like this :
ALTER TABLE dbo.TableName ADD CONSTRAINT
DF_ConstraintName DEFAULT 0 FOR ColName
-
Instructions accepted. I should have checked a profiel of EM.
But, your syntax worked like a charm though and I do appreciate your help
-
NP.
Give a guy a fish and feed him for one day, teach him to fish and feed him for life. :-).
(I know it's cheesy but it's still true).
-
I've found on occasion that EM scripts things very funny. I did some research and found that some syntaxes that aren't supported in all versions that EM supports will not be used. It uses the least common demonitator for syntax.
-
ya the problem comes mostly for table updates that are recreated. but for simple things like this it seems to do the job right.
-
It uses the least common demonitator for syntax.
I knew there have to be some demons in the SQL!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply