-- ************************************************************************
-- Generate a script to empty a SQL Server Database
-- By TheDummyProgrammer (https://www.thedummyprogrammer.com)
-- ************************************************************************
SET NOCOUNT ON;


IF OBJECT_ID('tempdb..#TO_BE_EXCLUDED') IS NOT NULL 
	DROP TABLE #RELS

-- Preparare the table containing the tables to exclude
CREATE TABLE #TO_BE_EXCLUDED
(
	TABLE_NAME		VARCHAR(100)
)

-- Add here all the tables that you don't want to delete
INSERT INTO #TO_BE_EXCLUDED(TABLE_NAME) VALUES('sysdiagrams')
INSERT INTO #TO_BE_EXCLUDED(TABLE_NAME) VALUES('__RefactorLog')


IF OBJECT_ID('tempdb..#RELS') IS NOT NULL 
	DROP TABLE #RELS

-- Prepare the table containing the database relationships
CREATE TABLE #RELS
(
	PARENT			VARCHAR(100),
	REFERENCED		VARCHAR(100),
	CODE			INT,
	CIRCULAR		INT
)

INSERT INTO #RELS(PARENT, REFERENCED, CODE, CIRCULAR)
	SELECT PARENT, REFERENCED, ROW_NUMBER() OVER(ORDER BY PARENT), 0
	FROM
	(
		SELECT	DISTINCT 
				OBJECT_NAME(FK.parent_object_id)		AS PARENT, 
				OBJECT_NAME(FK.referenced_object_id)	AS REFERENCED
		FROM sys.foreign_keys FK
	) T


-- Assign the same code to circular references
UPDATE #RELS
SET CODE = (SELECT MIN(CODE)
			FROM #RELS R_IN 
			WHERE R_IN.PARENT = #RELS.PARENT  AND R_IN.REFERENCED = #RELS.REFERENCED
					OR R_IN.PARENT = #RELS.REFERENCED AND R_IN.REFERENCED = #RELS.PARENT)

-- Mark circular references
UPDATE #RELS
SET CIRCULAR = 1
WHERE CODE IN (SELECT CODE
				FROM #RELS
				GROUP BY CODE 
				HAVING COUNT(*) > 1
				)

IF OBJECT_ID('tempdb..#DBTABLES') IS NOT NULL 
	DROP TABLE #DBTABLES


CREATE TABLE #DBTABLES
(
	TABLE_NAME		VARCHAR(100)
)

INSERT INTO #DBTABLES(TABLE_NAME)
	SELECT [name] AS TABLE_NAME
	FROM sys.tables

IF OBJECT_ID('tempdb..#DBTABLE_LEVELS') IS NOT NULL 
	DROP TABLE #DBTABLE_LEVELS

;WITH 
	RELS
	AS
	(
		-- Get a new table removing second instances of circular references
		SELECT PARENT, REFERENCED, CODE, CIRCULAR
		FROM
		(
			SELECT PARENT, REFERENCED, CODE, CIRCULAR, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY PARENT) RN
			FROM #RELS
		) AS T
		WHERE T.RN = 1
	),
	DBTABLE_LEVELS
	AS
	(
		-- Start from tables that don't reference other tables...
		SELECT T.TABLE_NAME, CONVERT(VARCHAR(MAX), T.TABLE_NAME) AS TABLE_PATH, R.CIRCULAR, 0 AS TABLE_LEVEL
		FROM #DBTABLES T
		LEFT JOIN RELS R ON T.TABLE_NAME = R.PARENT
		WHERE R.PARENT IS NULL
		UNION ALL
		SELECT R.PARENT, CONVERT(VARCHAR(MAX), DL.TABLE_PATH) + '\' + R.PARENT, R.CIRCULAR, DL.TABLE_LEVEL + 1
		FROM DBTABLE_LEVELS DL
		INNER JOIN RELS R	ON DL.TABLE_NAME = R.REFERENCED
		WHERE	NOT CONVERT(VARCHAR(MAX), DL.TABLE_PATH) LIKE '%\' + R.PARENT + '\%'
			AND NOT RIGHT(CONVERT(VARCHAR(MAX), DL.TABLE_PATH), LEN(R.PARENT)) = R.PARENT
	)
SELECT DISTINCT TABLE_NAME, CIRCULAR, TABLE_LEVEL
INTO #DBTABLE_LEVELS
FROM DBTABLE_LEVELS
ORDER BY TABLE_LEVEL DESC


DECLARE @TABLE_NAME		VARCHAR(100)
DECLARE @CIRCULAR		INT
DECLARE @TABLE_LEVEL	INT
DECLARE @TABLE_LEVEL_PREV	INT

DECLARE CUR CURSOR FOR 
	SELECT D.TABLE_NAME, D.CIRCULAR, D.TABLE_LEVEL
	FROM #DBTABLE_LEVELS D
	LEFT JOIN #TO_BE_EXCLUDED EXC ON D.TABLE_NAME = EXC.TABLE_NAME
	WHERE EXC.TABLE_NAME IS NULL
	ORDER BY TABLE_LEVEL DESC

OPEN CUR

FETCH NEXT FROM CUR INTO @TABLE_NAME, @CIRCULAR, @TABLE_LEVEL
SET @TABLE_LEVEL_PREV = ''

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @TABLE_LEVEL_PREV <> @TABLE_LEVEL
	BEGIN
		SET @TABLE_LEVEL_PREV = @TABLE_LEVEL
		PRINT '-- -------------'
		PRINT '-- TABLE_LEVEL: ' + CONVERT(VARCHAR, @TABLE_LEVEL)
		PRINT '-- -------------'
	END
		
	IF @CIRCULAR = 1
		PRINT '-- CIRCULAR REFERENCE DETECTED FOR TABLE ' + @TABLE_NAME

	PRINT 'DELETE FROM ' + @TABLE_NAME

	FETCH NEXT FROM CUR INTO @TABLE_NAME, @CIRCULAR, @TABLE_LEVEL
END

CLOSE CUR
DEALLOCATE CUR
