Modify Default value in SQL Server

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.

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

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

You should drop the DEFAULT constraint and add a new one like this

alter table Mytable
drop constraint <constraint name>
go
alter table MyTable
add constraint df_MyTable_MyColumn default -1 for MyColumn
go

Use sp_helpconstraint MyTable to get constraint names

DEFAULT is a constraint. You want to use ALTER TABLE ADD CONSTRAINT.

ALTER TABLE MyTable
ADD CONSTRAINT <constraint name>
DEFAULT -1 FOR MyColumn

You'll need to drop the current constraint first, like:

ALTER TABLE MyTable
DROP CONSTRAINT <constraint name>

Add default value for column if it is not exists

DECLARE @defaultLock VARCHAR(100)
SELECT @defaultLock = (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name = 'Lock' AND object_id = object_id('dbo.Positions'))
    IF @defaultLock IS NULL
        ALTER TABLE Positions ADD DEFAULT (0) FOR Lock

I've had to change the default constraint on many fields in existing databases so wanted do do it in one hit. Hope this helps...

select 'USE ' + DB_NAME()
SELECT 'ALTER TABLE '+ src3.name + '.' + src1.name +' drop constraint '+ src.name + '
ALTER TABLE ' + src3.name + '.' +  src1.name +' ADD CONSTRAINT '+ src.name + ' DEFAULT getutcdate() FOR ' + src2.name -- amend default constrint accordingly.
    FROM sys.tables src1
	  inner join sys.schemas src3
	    on src3.schema_id = src1.schema_id
	  inner join sys.columns src2
	    on src2.object_id = src1.object_id
	  inner join sys.default_constraints src
	    on src.parent_column_id = src2.column_id
		   and src.parent_object_id = src1.object_id
    WHERE  (-- pick up existing constraint definition
            src.definition like '%getdate%'
			 or src.definition like '%current\_timestamp%' escape '\'
			)
			and src1.type = 'U'
order by  src3.name + '.' + src1.name

Obviously you'll need to amend the query and copy the output to another tab to be executed.