Delete/Truncate data from all Tables in SQL Server

We usually use a dummy database for testing. It gets filled with a lot of unwanted temporary data. At the time of deployment we replicate this database into Live Database. While doing so we need to delete all data from each table of this database. It can be easily done if DELETE or TRUNCATE table doesn’t has a foreign key relationship. In this case, first of all we have to disable all CHECKs and TRIGGERs for that table and then call DELETE or TRUNCATE syntax. As this is a tedious and hectic task, MS SQL SERVER provides a function called sp_MSForEachTable, also called as undocumented sp_MSforeachtable procedure. This procedure is popularly used for performing the action on all the tables within the database.

Let’s see how to use this function to Empty your database:

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’
EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER  all’ EXEC sp_MSForEachTable ‘DELET FROM ?’ EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT all’
EXEC sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER  all’

On executing the above code,  data from all tables within the database gets deleted. For Truncating data from all tables we need to follow the steps explained below:

Create Procedure dbo.sp_TruncateAllTables (@ResetFlag Bit) As
Begin
Declare @SQLQUERY VarChar(500) Declare @TableName VarChar(255) Declare @ConstraintName VarChar(500) Declare AllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type=’FOREIGN KEY’ Open AllForeignKeys Fetch Next From AllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQLQUERY = ‘ALTER TABLE ‘ + @TableName + ‘ NOCHECK CONSTRAINT ‘ + @ConstraintName Execute(@SQLQUERY)
Fetch Next From AllForeignKeys 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 @SQLQUERY = ‘DELETE FROM ‘ + @TableName
If @ResetFlag = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),’TableHasIdentity’)=1 Set @SQLQUERY = @SQLQUERY + ‘; DBCC CHECKIDENT(”’ + @TableName + ”’,RESEED,0)’
Execute(@SQLQUERY)
Fetch Next From curAllTables INTO @TableName
End
Fetch First From AllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQLQUERY = ‘ALTER TABLE ‘ + @TableName + ‘ CHECK CONSTRAINT ‘ + @ConstraintName
Execute(@SQLQUERY)
Fetch Next From AllForeignKeys INTO @TableName,@ConstraintName
End
Close curAllTables
Deallocate curAllTables
Close AllForeignKeys
Deallocate AllForeignKeys
End

In the above procedure we need to pass a bit value (i.e. 0 or 1) which is used to know whether it is called for DELETE or TRUNCATE. 0 stands for DELETE and 1 stands for TRUNCATE. According to this value, procedure resets the SEED value of identity fields while calling TRUNCATE command.  In above procedure we collect the information about the CONSTRAINT of each table from Information_Schema.Table_Constraints. If table contains  ’TableHasIdentity’ and we execute the procedure for truncate then it resets the identity field by executing another extra syntax DBCC CHECKIDENT(”’ + @TableName + ”’,RESEED,0)’ otherwise it simply deletes data from the table.

Thus knowing the advantages of DELETE over TRUNCATE, most developers prefer to use DELETE.

This entry was posted in Database, MySQL and tagged , , , . Bookmark the permalink.

Leave a Reply