How to drop a list of SQL Server tables, ignoring constraints?

Question

I have a list of half a dozen MSSQL 2008 tables that I would like to remove at once from my database. The data has been entirely migrated to new tables. There is no reference in the new tables to the old tables.

The problem being that old tables comes with loads of inner FK constraints that have been autogenerated by a tool (aspnet_regsql actually). Hence dropping manually all constraints is a real pain.

How can I can drop the old tables ignoring all inner constraints?

Answer 1

It depends on how you want to drop the tables. If list of tables need to drop covers almost above 20 % of tables under your DB.

Then I will disable all the constraints in that DB under my script and drop the tables and Enable the constraints under the same script.

--To Disable a Constraint at DB level

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Write the code to DROP tables

DROP TABLE TABLENAME

DROP TABLE TABLENAME

DROP TABLE TABLENAME

--To Enable a Constraint at DB level

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Finally to check the Status of your constraints fire up this Query.

--Checks the Status of Constraints

SELECT (CASE 
    WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
    ELSE 'DISABLED'
    END) AS STATUS,
    OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
    OBJECT_NAME(FKEYID) AS TABLE_NAME,
    COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
    OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
    COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

If you dont want to disable the constraints at Database level then make a list of tables which you want to drop.

Step1 : Check the Constraints associated with thos tables

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Tablename')

Step2 : Disable the Constraints which are associated with these tables.

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

Step3 : Drop the tables

DROP TABLE TABLENAME

Answer 2

A simple DROP TABLE dbo.MyTable will ignore all constraints (and triggers) except foreign keys (unless you drop the child/referencing table first) where you may have to drop these first.

Edit: after comment:

There is no automatic way. You'll have to iterate through sys.foreign_keys and generate some ALTER TABLE statements.

Answer 3

Run the following script to delete all the constraints in all tables under current DB and then run the drop table statements.

DECLARE @dropAllConstraints NVARCHAR(MAX) = N'';

SELECT @dropAllConstraints  += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + 
    ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;
EXEC sp_executesql @dropAllConstraints