André Kienitz

A default constraint in a table defines a value for a column where no value is specified. You can name a default constraint as in the example given:

create table dbo.mytable (
id integer
, myvalue integer constraint myconstraintname default 77
)

We created a table with a constraint on the column myvalue and the constraint is named myconstraintname.
Now you can add a row to that table without giving a value for the column myvalue:

insert into dbo.mytable (id) values (1)

The table has the following row:

select *
from dbo.mytable

Result set 1

When the constraint is no longer needed, it is quite easy to delete the constraint as you can use the constraint name.

alter table dbo.mytable
drop constraint myconstraintname

Let’s consider another table with the same structure. But this time we do not name the constraint.

create table dbo.mytable2 (
id integer
, myvalue integer default 77
)

Here it is also possible to add a row without giving a value for the columnname myvalue:

insert into dbo.mytable2 (id) values (1)

We can see that the table has one row:

select *
from dbo.mytable2

Result set 2

Now we want to delete the default constraint without creating a new table or without deleting the column myvalue. As we do not have a constraint name we cannot use the same statement as for the first table.

We did not name our constraint. But nevertheless the constraint is stored in the system tables of sql server. Sql server gave the constraint an internal name that we can use. The following sql statement shows where we can find the internal name of our constraint:

select t4.name
, t4.object_id
, t4.schema_id
, t4.parent_object_id
, t4.type
, t4.type_desc
, t2.name as schemaname
, t1.name as tablename
, t3.name as columnname
from [sys].[tables] t1
join [sys].[schemas] t2
on t1.schema_id = t2.schema_id
join [sys].[all_columns] t3
on t1.object_id = t3.object_id
join [sys].[default_constraints] t4
on t1.object_id = t4.parent_object_id
and t3.column_id = t4.parent_column_id
where t1.name = N'mytable2' -- table name
and t2.name = N'dbo' -- schema name
and t3.name = N'myvalue' -- column name

Result set 3

The internal default constraint name is called „DF__mytable2__myvalu__6FB560CC“.

Now you can construct the alter table statement using the system tables of sql server:

declare @cmd nvarchar(150)

select @cmd = 'ALTER TABLE ' + t2.name + '.' + t1.name + ' DROP CONSTRAINT ' + t4.name
from [sys].[tables] t1
join [sys].[schemas] t2
on t1.schema_id = t2.schema_id
join [sys].[all_columns] t3
on t1.object_id = t3.object_id
join [sys].[default_constraints] t4
on t1.object_id = t4.parent_object_id
and t3.column_id = t4.parent_column_id
where t1.name = N'mytable2' -- table name
and t2.name = N'dbo' -- schema name
and t3.name = N'myvalue' -- column name

exec(@cmd)

The script generates the following statement:

ALTER TABLE dbo.mytable2 DROP CONSTRAINT DF__mytable2__myvalu__6FB560CC

The statement is stored in the variable @cmd and finally the content of the variable @cmd is executed deleting the default constraint.