Deleting All Data From All User Tables In A Database

Aaron Alton - May 2, 2008

CREATE PROCEDURE DeleteAllData
AS
BEGIN
DECLARE @SQL nvarchar(2000), @CurrentTable sysname, @CurrentSchema sysname
 
--Grab the server version for any statements which need to be modified based upon the server version
DECLARE @ServerVersion int
SET @ServerVersion = (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar(50)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar(50))) - 1) AS int))
 
--This solution entails a cursor.  Alternatively, it could be done with
--the undocumented stored procedure sp_msforeachtable, or with loop logic.
	DECLARE TableCursor SCROLL CURSOR FOR
	SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
 
--Disable all triggers first
WHILE @@FETCH_STATUS = 0
BEGIN
	--Create a TSQL string to disable triggers on the current table
	SET @SQL = 
	(SELECT	'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' DISABLE TRIGGER ALL')
	EXECUTE sp_ExecuteSQL @SQL;
	
	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Triggers successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling triggers on ' + @CurrentSchema + '.' + @CurrentTable
		END
		
	--Create a TSQL string to disable constraints on the current table
	SET @SQL = 
	(SELECT	'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' NOCHECK CONSTRAINT ALL')
	EXECUTE sp_ExecuteSQL @SQL;
	
	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Constraints successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling constraints on ' + @CurrentSchema + '.' + @CurrentTable
		END
 
	--Fetch the next table from the cursor
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
	
--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
	
	IF @ServerVersion >= 9  --IF we're on SQL 2005 or greater, we can use Try/Catch.
		BEGIN
		SET @SQL = (SELECT	'BEGIN TRY 
		TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' 
		PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
		END TRY 
		BEGIN CATCH 
		DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
		IF EXISTS(SELECT ''A'' FROM information_schema.columns 
			WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), 
			column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
			AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
			BEGIN
				DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) 
			END
		PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
		END CATCH')
		END
	ELSE  --We're on SQL 2000, so we need to check for foreign key existence first.
		BEGIN
		SET @SQL = (SELECT	'IF OBJECTPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), ''TableHasForeignRef'') <> 1
			BEGIN
			TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' 
			PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
			END
		ELSE
			BEGIN
			DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
			IF EXISTS(SELECT ''A'' FROM information_schema.columns 
				WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), 
				column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
				AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
				BEGIN
					DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) 
				END
			PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
			END')
		END
		
	EXECUTE sp_ExecuteSQL @SQL;
	
	--Fetch the next table from the cursor		
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
 
--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable
 
WHILE @@FETCH_STATUS = 0
BEGIN
	--Reenable triggers
	SET @SQL = (SELECT	'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' ENABLE TRIGGER ALL')
	EXECUTE sp_ExecuteSQL @SQL;
 
	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Triggers successfully reenabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while reenabling triggers on ' + @CurrentSchema + '.' + @CurrentTable
		END
		
	--Now reenable constraints
	SET @SQL = (SELECT	'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' CHECK CONSTRAINT ALL')
	EXECUTE sp_ExecuteSQL @SQL;
 
	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Constraints successfully disabled on ' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling constraints on ' + @CurrentTable
		END
 
 
	--Fetch the next table from the cursor		
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
 
--CLOSE and DEALLOCATE our cursor
CLOSE TableCursor
DEALLOCATE TableCursor
 
END
 
--EXEC DeleteAllData

Stored Procedure Notes

• The stored procedure first disables checking on each table's constraints and triggers. It then deletes or truncates the data in the table (based upon whether or not there is a foreign key relationship). Finally, the trigger and constraint checking are reenabled.
• The code diverges based upon SQL Server Version to perform the DELETE/TRUNCATE logic: with SQL 2005 and greater, it uses TRY...CATCH. With SQL 2000, it uses OBJECTPROPERTY to determine whether a truncate or a delete call is warranted.
• Simply deleting data does NOT necessarily reduce the size of the database files and SQL transaction logs. See DBCC Shrinkfile for more information.