CODEWITHSUNDEEP
sql-server-2008
Fedor Hajdu
Fedor Hajdu

Reputation: 4695

SQL Server - altering column default value

I need to alter a default value for a column in an existing table.

I assumed (and I actually found a few examples on the net "confirming" this) that I could do ALTER TABLE table ALTER COLUMN column SET DEFAULT 'something' but I get syntax error and according to this http://msdn.microsoft.com/en-us/library/ms190273.aspx DEFAULT is not supported.

Next idea was to drop the default constraint from the database but I found that when the table was created the constraint was not named and SQL generates a random name with every deployment so I can't reference it by name.

I came up with a query from sys tables that returns the name of the constraint:

select o.name from sys.objects o 
join sys.columns c on o.object_id = c.default_object_id
join sys.tables t on c.object_id = t.object_id
where t.name='TableName' and c.name='ColumnWithDefValue'

I'm about to write a query by string concatenation and I can't stop thinking about how there must be a better way to do all this. Any suggestions?

TL;DR version: what's the easiest way to alter a default constraint for a column in SQL Server R2?

Thanks

Upvotes: 1

Views: 4494

Answers (1)

Matt Smucker
Matt Smucker

Reputation: 5244

Looks like you have to drop the constraint and then recreate it

http://bypsoft.blogspot.com/2007/10/changing-default-column-values-sql.html

Upvotes: 1

Related Questions