Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

As a developer, many times we need to delete all the records from all tables. For example we are developing one application in our development server. At the time of developing we must have entered lots of temporary data in our database; we shouldn’t upload all those data along with structure at live server. We should have to delete all dummy and temp data.

What will you do in that case? Execute delete statement for all foreign key table first and then delete from master tables????? Or else remove all constraint, check and triggers from all tables and then execute delete statement for all tables. Uuuufffffffff so tedious and boring job!!!! Isn’t it??????

So now we will examine other way of deleting all rows from all tables in short route with the help of sp_MSforeachtable (undocumented stored procedure of Microsoft Sql-Server 2005). 

 let us see, how we can do empty the tables.

CREATE PROCEDURE sp_EmplyAllTable

AS

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

EXEC sp_MSForEachTable ‘DELETE FROM ?’

EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

First we will disable all the constraint in all the tables then we will delete all the records from all the tables and then enable the constraint back again.