[T-SQL] Drop all constraints on a table
Here's a simple scriptlet I've included in database upgrade scripts to drop all constraints on a table which will then allow dropping the table. Of course, you'll want to replace the @database and @table parameters.
This doesn't take care of circumstances where another table has a foreign key to the table you want to drop - in that case, you'll want to explicity drop that foreign key first.
-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)
set @database = 'dotnetnuke'
set @table = 'tabs'
DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
where constraint_catalog = @database and
table_name = @table
exec sp_executesql @sql