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