Deleting All Data From All User Tables In A Database

Aaron Alton - May 2, 2008

For various reasons, one may need to delete all rows from all tables in a SQL Server database. If the number of tables in a database is great, this task can become quite tedious if performed manually. Additionally, triggers and constraints which enforce referential integrity must be considered if DELETE or TRUNCATE statements are to succeed. The solution below provides a stored procedure (working with with SQL 2000/2005/2008) which, when run, will delete ALL data from ALL tables in the current database. Please use with appropriate caution.

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