CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.

Notice the unnamed default in the following line:

alter table test add col1 int not null default 1

This creates a default constraint in the database that is named something like "DF__test__col1__182C9B23," which is pretty ugly. The last several characters are some sort of random-ish sequence, and will be different on everybody's database. The difficulty comes later if some needs to modify this table:

alter table test drop column col1

You'll get the error:

The object 'DF__test__col1__182C9B23' is dependent on column 'col1'.

ALTER TABLE DROP COLUMN col1 failed because one or more objects access this column.

So to drop the column you have to first drop the constraint:

alter table test drop constraint DF__test__col1__182C9B23

Checking this script into source control is probably a big mistake. That script will only work on my database - everyone else will get this error, because their constraint is named something else. They'll get the following error:

'DF__test__col1__182C9B23' is not a constraint.

Could not drop constraint. See previous errors.

So rewind to the beginning. Instead of this:

alter table test add col1 int not null default 1

Do this:

alter table test add col1 int not null constraint DF_test_col1 default 1

Now everyone's default constraint is named the same, and it's trivial to write a script that will drop the constraint on anyone's database. I think it's a fairly standard naming convention for default constraints to be DF_[table name]_[column name].

By the way, here is the definition of a stored procedure that can be used to drop unnamed default constraints.

create proc DropColumnDefault (
      @table_name nvarchar(256),
      @col_name nvarchar(256)
) as
declare @Command  nvarchar(1000)
select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
where t.name = @table_name and c.name = @col_name
if @Command is not null
execute (@Command)