Script to find the default constraint and drop

Here is the script that you can use for dropping the default constraints when you do not know the name of the default constraint. You have to key in your table name and column name before you execute it.

declare @tmp_def varchar(254),
@tmp_sql varchar(254)
select    @tmp_def=c_obj.name
from sysobjects    c_obj
join sysobjects    t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id    = con.constid
join syscolumns    col on t_obj.id = col.id and con.colid = col.colid
where    t_obj.name='YOUR TABLE NAME'
and col.name = 'YOUR COLUMN NAME'
set @tmp_sql = 'alter table <YOUR TABLE NAME> drop constraint '+ @tmp_def
print @tmp_sql
exec (@tmp_sql)
go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: