Drop all the tables without any Constraint Error in SQL SERVER 2008

Question

I want to DROP all the tables with name starts with particular name, I wrote the script for the same but, when I try to delete the TABLE , I am getting Constraint issue.

So I want the approach to drop each table, in loop without/ignoring Relationships/Forieng_Key. Please suggested optimized script to perform the same operation. Please share your thoughts.

    DECLARE @SqlStatement VARCHAR(MAX)

    SET @SqlStatement = ''
    Print 'Deleting Tables and Columns from the Agency Table schema'
    SELECT @SqlStatement = 
        COALESCE(@SqlStatement, '') + 'DROP TABLE ['+@agencyName+'].' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = @agencyName;

    PRINT @SqlStatement
    --exec(@SqlStatement) -- In this Line I am getting the Foreign Key constraint issue. how do I achieve the functionality.

    --DROP SCHEMA Agency3

you could disable the foreign key constraints: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — deterministicFail, Sep 01 '14 at 07:26
But the problem is automation, I donno whether table is foreign key table or priamry key table, I want some generic approach for the same — Akshay Joy, Sep 01 '14 at 07:27
you can search all foreign keys for your tables and disable them, after that you can drop your tables — deterministicFail, Sep 01 '14 at 07:35
What If when the iteartion stars with Primary Column table .. — Akshay Joy, Sep 01 '14 at 08:24
you iterate first over the foreign keys, and then you iterate over the tables. The PK tables doesnt matter then — deterministicFail, Sep 01 '14 at 08:46

Drop all the tables without any Constraint Error in SQL SERVER 2008

0 Answers0