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

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 2

I suspect that you would have to do an 'alter' command on the offending tables before the drop to remove the forigen key contraints.

ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
DROP TABLE Orders;

Of course if you drop the child tables first, then you wont have this problem. (unless you have table A contraint to table B and table B constraint to A, then you will need to Alter one of the tables, e.g. A to remove the constraint)

e.g. this WONT work, since Orders has a contraint from Order_Lines

DROP TABLE Orders;
DROP TABLE Order_lines;

e.g. this will work

DROP TABLE Order_lines;
DROP TABLE Orders;

Answer 3

I finally found the solution based on the script provided by Jason Presley. This script automatically removes all constraints in the DB. It's easy to add a WHERE clause so that it only applies to the set of concerned tables. After that, dropping all tables is a straightforward.

Answer 4

Be very careful with the following script, all tables, views, functions, stored procedures and user defined types from a database ignoring all constraints.

/*
  Description: This script will remove all tables, views, functions, stored procedures and user defined types from a database.
*/
        declare @n char(1)
        set @n = char(10)

        declare @stmt nvarchar(max)

        -- procedures
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.procedures


        -- check constraints
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
        from sys.check_constraints

        -- functions
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop function [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.objects
        where type in ( 'FN', 'IF', 'TF' )

        -- views
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop view [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.views

        -- foreign keys
        select @stmt = isnull( @stmt + @n, '' ) +
        'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
        from sys.foreign_keys

        -- tables
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop table [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.tables

        -- user defined types
        select @stmt = isnull( @stmt + @n, '' ) +
        'drop type [' + schema_name(schema_id) + '].[' + name + ']'
        from sys.types
        where is_user_defined = 1


        exec sp_executesql @stmt

Answer 5

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 

Answer 6

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 7

I found a reasonable(ish) way to do it by making SQL write the SQL to drop the constraints:

select concat("alter table ", table_name, " drop ", constraint_type ," ", constraint_name, ";")
  from information_schema.table_constraints 
  where table_name like 'somefoo_%' 
        and 
        constraint_type <> "PRIMARY KEY";

You will want to modify the table name to suit your needs, or possibly select against other column/values.

Also, this would select any non primary key constraint, which might be too big of a sledgehammer. Maybe you need to just set it to =?

I am not a DBA. there may be better ways to do this, but it worked well enough for my purposes.