DROP All Tables

Dropping all tables is not that straight forward:

• There are constraints keeping you from dropping referenced tables → Disable checking them
• There are foreign key constraints referencing primary ones → Drop foreign key constraints first
• Drop primary key constraints then
• Only then can we safely drop the tables themselves

This script will select the all the necessary meta information to create the commands to execute later. We do need to ensure that the sequence is retained,, thus the Prio. Then we let a coursor run over all those commands and execute them.

DECLARE cmds CURSOR FOR
    WITH [TmpDropCmds] AS (
        SELECT 
            10 AS Prio,
            'ALTER TABLE ['+TABLE_NAME+'] NOCHECK CONSTRAINT ALL' AS SqlCmd
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE IN ('BASE TABLE')
        UNION
        SELECT 
            20 AS Prio,
            'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+CONSTRAINT_NAME+']' AS SqlCmd
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_NAME LIKE 'FK_%'
        UNION
        SELECT 
            30 AS Prio,
            'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+CONSTRAINT_NAME+']' AS SqlCmd
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_NAME NOT LIKE 'FK_%'
        UNION
        SELECT
            50 AS Prio,
            CASE WHEN TABLE_TYPE = 'VIEW' THEN
                ('DROP VIEW ['+TABLE_NAME+']')
            ELSE
                ('DROP TABLE ['+TABLE_NAME+']')
            END AS SqlCmd
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE IN ('VIEW', 'BASE TABLE')
    )
    SELECT SqlCmd
    FROM [TmpDropCmds]
    ORDER BY Prio

DECLARE @cmd VARCHAR(MAX)

OPEN cmds
FETCH NEXT FROM cmds INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@cmd)
    FETCH NEXT FROM cmds INTO @cmd
END

CLOSE cmds
DEALLOCATE cmds