drop all constraints on a table

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)
set @database = ‘test’
set @table = ‘test’
DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
select   @sql = ‘ALTER TABLE ‘ + @table + ‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME
from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where   constraint_catalog = @database and table_name = @table
exec    sp_executesql @sql
END
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