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??????

Actually one of my friends Bihag Thaker has developed one script to do the same stuff and he asked me once for the better way of doing so. I tried roaming in world of internet here and there and finally found the way of doing the same thing with less effort. You can see script developed by Bihag Thaker at:
http://bihagthaker.blogspot.com/2008/08/how-to-empty-all-tables-in-database.html

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). You can further see the article of sp_MSforeachtable from following blog wrote by me.

http://riteshshah.wordpress.com/2009/03/04/sp_msforeachtable-undocumented-but-useful-stored-procedure-for-auto-iteration-in-sql-server-2005/

or

http://ritesh-a-shah.blogspot.com/2009/03/spmsforeachtable-undocumented-but.html

Now 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.

Magical code of three lines… isn’t it??!!!

You can further develop above stored procedure for your customize need.

Note: Do not try to attempt this SP in your live database

Reference: Ritesh Shah