Script to reset DB.

Having a script to delete contents of all tables in the DB of your application is handy -
to clean the test data from DB .

(I'm talking in this post about Sql Server 2000)

So far I've used a script which contained a bunch of DELETE statements in the right order ( according to the FK's).
( In my case I always leave some tables non-touched - for example : USERS,GROUPS and etc' - just for not entering all this info again every time I clean the DB)

But since the application is under development - new tables are added all the time.
And I became annoyed by the need to update the script with each new table.

So I wanted to write some kind of script that will delete data from all tables in the DB, besides those I specify .

This way the script almost never needs an update.

I did it in the following way :

exec sp_msforeachtable

@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL",

@command2 = "DELETE ?",

@command3 = "ALTER TABLE ? CHECK CONSTRAINT ALL",

@whereand = ' and name not like ''%LUT%'' and name not in(''USER'',''USER_GROUP'',''SECTION_GROUP'')'

Another option is to write code with CURSOR which runs over all the tables you want (taking it from the sysobjects table ) .

But this is simpler , since sp_msforeachtable
does that for you.

I wasn't familiar with sp_msforeachtable untill now.
Here some info on this sp and other useful undocumented Sql Server sp's :

http://www.databasejournal.com/features/mssql/article.php/1490661

http://www.databasejournal.com/features/mssql/article.php/3441031

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm