Empty Database

Further to my last – here is my script to empty (using the very fast TRUNCATE command) all the tables that are not referenced by a foreign-key. Then in (2) attempt to delete the contents of those remaining (This fails for tables that are referenced by non-empty tables). Step-3 has another go at those that failed, hopefully more successfully now the referencing tables are empty.

-- EmptyDatabase.sql

-- 1) TRUNCATE child & independent tables

-- capture non-parent table names
SELECT SCHEMA_name(schema_id) [schema], [name] INTO #tables
FROM sys.tables
where name NOT IN (SELECT OBJECT_NAME(referenced_object_id) FROM sys.foreign_keys)
ORDER BY name

-- loop through list truncating tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #tables) > 0
BEGIN
	SELECT TOP 1 @sql = [schema] + '.[' + [name] + ']' FROM #tables
	DELETE FROM #tables WHERE [schema] + '.[' + [name] + ']' = @sql
	SELECT @sql = 'truncate table ' + @sql
	EXEC(@sql)
END

DROP TABLE #tables


-- 2) DELETE parents of child tables

--list all parent-tables
SELECT OBJECT_NAME(referenced_object_id) pname into #ptables
FROM sys.foreign_keys
GO

-- loop through list DELETING tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #ptables) > 0
BEGIN
	SELECT TOP 1 @sql = [pname] FROM #ptables
	DELETE FROM #ptables WHERE [pname] = @sql
	SET @sql = 'delete ' + @sql
	exec(@sql)
END

drop table #ptables


-- 3) DELETE parents of (now-empty) parent tables

--list all parent-tables
SELECT OBJECT_NAME(referenced_object_id) pname into #ptables2
FROM sys.foreign_keys
GO

-- loop through list DELETING tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #ptables2) > 0
BEGIN
	SELECT TOP 1 @sql = [pname] FROM #ptables2
	DELETE FROM #ptables2 WHERE [pname] = @sql
	SET @sql = 'delete ' + @sql
	exec(@sql)
END

drop table #ptables2

In use, this reduced a database of [965,849,336] total rows of data to just [10,860] rows in less than two seconds. Which after shrinking (this was a copy of a production-database on a dev-server set to Simple-mode), shrunk it from [822000.50 MB] to [65100.13 MB].

I think I deserve a beer!