Take a look at this code

create table Foo2(id int,
id2 int constraint DefaultID2 default 1)

As you can see it is a simple table with 2 columns, the second column has a constraint on it named DefaultID2.

We can verify that the table has a default on that column by writing something like this

select column_default 
from information_schema.columns 
where table_name = 'Foo2' 
and column_name = 'id2'

this is the output

((1))

How can you get the default name?

On version 7 and up run this

select s.name --default name
from sysobjects s 
join syscolumns c on s.parent_obj = c.id
where s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= object_id('Foo2')
and c.name ='id2'

On 2005 and up run this(the previous code also runs on SQL Server 2005 and SQL Server 2008)

select s.name --default name
from sys.sysobjects s 
join sys.syscolumns c on s.parent_obj = c.id
where s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= object_id('Foo2')
and c.name ='id2'

How do you drop such a constraint? Very easy you do this

ALTER table foo2 drop DefaultID2

Now we will create a table with two default constraints and both of them will be created without a name when running the create table DDL

create table Foo(id int default 0,
id2 int default 1)

Now let’s see if the default is created

select column_default 
from information_schema.columns 
where table_name = 'Foo' 
and column_name = 'id2'

This still returns this output

((1))

All fine, now how can we drop the default for the id2 column on the Foo table?

Running this code

select s.name --default name
from sys.sysobjects s 
join sys.syscolumns c on s.parent_obj = c.id
where s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= object_id('Foo')
and c.name ='id2'

Will give use the default name, in this case it is DF__Foo__id2__7D439ABD

So now we can drop the default by doing this

ALTER table foo2 drop DF__Foo__id2__7D439ABD

So what is the big deal you say?

Let’s say you do this on a staging box and want to create a script to hand over to someone else who will run it on the production box

If you create you script on the staging box and the person runs it on production he will see something like this

erver: Msg 3733, Level 16, State 2, Line 1

Constraint ‘DF__Foo__id2__7D439ABD’ does not belong to table ‘foo2’.

Server: Msg 3727, Level 16, State 1, Line 1

Could not drop constraint. See previous errors.

Then you will get a call that your script is broken, you will tell him that it works on staging. In the end you will have to do something like this so that it can run on any server as long as the table and column name are the same

declare @default sysname
declare @tableName sysname
declare @columnname sysname

select @tableName  = 'Foo' --table name
select @columnname = 'id2' --column name

--check for SQL Server Version
if coalesce(parsename(convert(varchar(50),SERVERPROPERTY ( 'ProductVersion' )),4),
	parsename(convert(varchar(50),SERVERPROPERTY ( 'ProductVersion' )),3)) > 8

	select @default= s.name --default name
	from sysobjects s 
	join syscolumns c on s.parent_obj = c.id
	where s.xtype = 'd'
	and c.cdefault = s.id
	and parent_obj= object_id(@tableName)
	and c.name =@columnname

else

	select @default= s.name --default name
	from sys.sysobjects s 
	join sys.syscolumns c on s.parent_obj = c.id
	where s.xtype = 'd'
	and c.cdefault = s.id
	and parent_obj= object_id(@tableName)
	and c.name =@columnname



--test first
print( 'alter table ' + @tableName +' drop  ' + @default )
--exec ( 'alter table ' + @tableName +' drop  ' + @default )

I commented out the exec and put print instead so that you can see what would get executed

A best practice is always to name your constraint because it will save you a lot of headaches down the road

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum