Delete all rows from all tables in a Sql Server database

Delete all rows from all tables in a Sql Server database may not be an easy task.

This because in a relational database you have foreign keys between tables, and to empty all tables you have to write delete statements in the correct order to avoid foreign key violations.

I know that there are scripts to disable and enable all foreign keys so you can easily delete the content of all tables, but suppose you cannot use such a script. The other way is trying to delete data in the correct order.

And here lies the problem: it can be difficult to delete the tables in the right order if the database has big number of tables with a big number of foreign keys.

It would be nice to have a something that tells us in which order we have to make delete statements. Moreover, given the possible presence of circular references between the tables, it would be useful to have evidence of these cases to be able to manage them manually.

The solution

So i decided to write a SQL script to do this job. The script broadly follow these steps:

populate a temporary tables with tables you want to exclude
read tables foreign keys from the view sys.foreign_keys
detect and mark circular references between tables
build the list of delete statements, using a recursive query that starts from tables that doesn’t reference other tables and recursively add referencing tables
Open a cursor and PRINT the delete statements

After the execution of the script, you will find the result in the tab “Messages” of Sql Server Management Studio.

Here is the script (download here):

-- ************************************************************************
-- 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 #TO_BE_EXCLUDED

-- 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

The solution

Related Posts