Modify Default value in SQL Server

Question

I'm trying to change the default value of a column using a SQL statement in SQL Server 2008. I've found in many places how to set the default value when you create a table/add a column but not how to set it/modify it once the column already exists.

This is what I can use to set it on adding:

ALTER TABLE MyTable ADD MyColumn int NOT NULL DEFAULT 0 

And that works, but if I try to modify it later:

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL DEFAULT -1 ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL SET DEFAULT -1 

None of those are syntactically correct, and I don't find the syntax to do what I pretend anywhere. The only option I come with is to add a new column, copy values from previous column, then remove previous column and new column to make the change, but that doesn't seem right to me.

Is there a way of doing what I want in just one simple sentence?

Thanks.

Answer 1

When you add a column with default value, what happens is that there's a default constraint being created:

create table _temp  ( x int default 1) 

sp_help _temp result:

constraint_type constraint_name DEFAULT on column x DF___temp__x__5A3B20F9 

So default clause in column definition is just a shortcut for creating a constraint; default value is not an intrinsic property of a column. If you want to modify default for already existing column, you have to drop the constraint first:

alter table _temp drop constraint DF___temp__x__5A3B20F9  

And then create a new default constraint:

alter table _temp add constraint DF_temp_x default 2 for x 

Answer 2

DECLARE @Command nvarchar(max), @ConstraintName nvarchar(max), @TableName nvarchar(max), @ColumnName nvarchar(max) SET @TableName = 'TableName' SET @ColumnName = 'ColumnName' SELECT @ConstraintName = name     FROM sys.default_constraints     WHERE parent_object_id = object_id(@TableName)         AND parent_column_id = columnproperty(object_id(@TableName), @ColumnName, 'ColumnId')  SELECT @Command = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName   EXECUTE sp_executeSQL @Command  SELECT @Command = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName + ' DEFAULT 1 FOR ' + @ColumnName  EXECUTE sp_executeSQL @Command