SQL Server - Delete Records From All Tables

Sometimes it is needed to delete all the records from all the tables resetting the SEED value of identity fields at the same time. This article will show you how we can achieve this task with the help of user defined stored procedure.

Sometimes it is exercised to empty all the tables in a database by deleting all the records from all the tables or by truncating every table in a database. This is indeed needed while we are developing a real life application and playing around with the database in order to carry out testing. But as it seems to be this task is not that much easy and trivial because usually there exists so many Primary Key-Foreign Key relationships between parent and child tables at many levels in such types of applications. If you start deleting records in improper sequence, records might not be deleted from every table successfully as depending upon the option ON DELETE CASCADE set on foreign tables records might not be deleted from parent tables as long as their corresponding child records are existing and you don’t want to be warned by SQL Server with the error message “The DELETE statement conflicted with the REFERENCE constraint…” every time you try to delete the records from some tables and at the same time you also don’t want to remember the complete hierarchy of parent-child relationships between all the tables in order to carry out delete operation in proper sequence. While emptying tables, sometimes we need to reset the SEED value of identity fields which the TRUNCATE command does it for us and DELETE command does not. But unfortunately TRUNCATE also does not work because as long as the foreign key constraints are existing, parent tables can’t to be truncated. Keeping all these in mind, I needed to create a solution which solves this problem.

Create Procedure dbo.sp_EmptyAllTables (@ResetIdentity Bit)

As

Begin

    Declare @SQL VarChar(500)

    Declare @TableName VarChar(255)

    Declare @ConstraintName VarChar(500)

    Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY'

    Open curAllForeignKeys

    Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

    While @@FETCH_STATUS=0

    Begin

        Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName

        Execute(@SQL)

        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

    End

    Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE'

    Open curAllTables

    Fetch Next From curAllTables INTO @TableName

    While @@FETCH_STATUS=0

    Begin

        Set @SQL = 'DELETE FROM ' + @TableName

            If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1

                  Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'             

        Execute(@SQL)

        Fetch Next From curAllTables INTO @TableName

    End

    Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName

    While @@FETCH_STATUS=0

    Begin

        Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName

        Execute(@SQL)

        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

    End

    Close curAllTables  

    Deallocate curAllTables

    Close curAllForeignKeys

    Deallocate curAllForeignKeys  

End

-----------------------

The above stored procedure accepts one parameter (@ResetIdentity) which can be either 0 or 1 depending upon whether we need to reset the SEED of identity fields or not (1 for resetting otherwise 0). To empty all the tables while resetting the SEED value of identity fields at the same time, just call this store procedure in the following way:

sp_EmptyAllTables 1