Delete all tables from schema

[overwraith]

This is surprisingly difficult. Consider it a snippet. Deletes all tables from a sql server schema. Useful for those times on your dev machine where you need to tear down all the cruft that has built up on your server due to refactoring of object relationally mapped tables.

--Author: Cameron Block
DECLARE @SCHEMA VARCHAR(50) = 'cblock';
DECLARE @LOOP_PREDICATE INT = 1;

WHILE (@LOOP_PREDICATE > 0)
BEGIN
    DECLARE CUR_PARENTS CURSOR FOR 
    select referenced_object_id "REF_ID", T.name "TABLE", S.name "SCHEMA"
    from sys.foreign_keys FK
    JOIN SYS.TABLES T ON T.object_id = FK.referenced_object_id
    JOIN sys.schemas s
        ON t.[schema_id] = s.[schema_id]
    JOIN INFORMATION_SCHEMA.TABLES I
        ON i.TABLE_NAME = t.name
        AND i.TABLE_SCHEMA = s.name
    WHERE s.name = @SCHEMA
    GROUP BY referenced_object_id, T.name, S.name;

    DECLARE CUR_NON_LINKED_TABS CURSOR FOR
    WITH PARENTS AS (
        select referenced_object_id "REF_ID", T.name "TABLE", S.name "SCHEMA"
        from sys.foreign_keys FK
        JOIN SYS.TABLES T ON T.object_id = FK.referenced_object_id
        JOIN sys.schemas s
            ON t.[schema_id] = s.[schema_id]
        JOIN INFORMATION_SCHEMA.TABLES I
            ON i.TABLE_NAME = t.name
            AND i.TABLE_SCHEMA = s.name
        WHERE s.name = @SCHEMA
        GROUP BY referenced_object_id, T.name, S.name
    ), 
    ALL_TABS AS (
        SELECT T.object_id "REF_ID", T.NAME "TABLE", S.name "SCHEMA"
        FROM   sys.tables t
        JOIN sys.schemas s
            ON t.[schema_id] = s.[schema_id]
        JOIN INFORMATION_SCHEMA.TABLES i
            ON i.TABLE_NAME = t.name
            AND i.TABLE_SCHEMA = s.name
        left join sys.foreign_key_columns as fc
            on t.object_id = fc.constraint_object_id
        left join sys.foreign_keys as f
            on f.object_id = fc.constraint_object_id
        WHERE  t.type = 'U' 
            AND i.TABLE_TYPE = 'BASE TABLE'
            AND s.name = @SCHEMA
    )
    SELECT * FROM ALL_TABS
    EXCEPT 
    SELECT * FROM PARENTS;

    --GET THE NUMBER OF REFERENCED TABLES LOOP UNTIL THEY ARE ALL DELETED
    OPEN CUR_PARENTS
    SET @LOOP_PREDICATE = @@CURSOR_ROWS;

    DECLARE @REF_ID INT
    DECLARE @TABLE NVARCHAR(100)
    DECLARE @SCH NVARCHAR(100)
    DECLARE @SQL_CMD NVARCHAR(MAX)

    OPEN CUR_NON_LINKED_TABS
    FETCH NEXT
    FROM CUR_NON_LINKED_TABS INTO @REF_ID, @TABLE, @SCH
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL_CMD = 'DROP TABLE [' + @SCH + '].[' + @TABLE + '];';

        EXEC sp_executesql @SQL_CMD
        FETCH NEXT
        FROM CUR_NON_LINKED_TABS INTO @REF_ID, @TABLE, @SCH
    END

    CLOSE CUR_NON_LINKED_TABS;
    DEALLOCATE CUR_NON_LINKED_TABS;

    CLOSE CUR_PARENTS;
    DEALLOCATE CUR_PARENTS;
END

[pty]

What's the advantage of this over drop schema myschema?

[overwraith]

I'll have to try that one. Ignorance is the answer.