Take a look at this code

T-SQL
1
2
create table Foo2(id int,
id2 int constraint DefaultID2 default 1)
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

T-SQL
1
2
3
4
select column_default 
from information_schema.columns 
where table_name = 'Foo2' 
and column_name = 'id2'
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

T-SQL
1
2
3
4
5
6
7
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'
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)

T-SQL
1
2
3
4
5
6
7
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'
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

T-SQL
1
ALTER table foo2 drop DefaultID2
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

T-SQL
1
2
create table Foo(id int default 0,
id2 int default 1)
create table Foo(id int default 0,
id2 int default 1)

Now let’s see if the default is created

T-SQL
1
2
3
4
select column_default 
from information_schema.columns 
where table_name = 'Foo' 
and column_name = 'id2'
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

T-SQL
1
2
3
4
5
6
7
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'
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

T-SQL
1
ALTER table foo2 drop DF__Foo__id2__7D439ABD
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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 )
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