How to empty all the tables in a database?

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.

I wrote the following stored procedure that performs this task for us. Just create this stored procedure in the database for which the tables need to be emptied.

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

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

I hope this technique will save some of your time needed to perform the task of emptying all the tables in a database.

-Bihag Thaker