I have a test environment for a database that I want to reload with new data at the start of a testing cycle. I am not interested in rebuilding the entire database- just simply "re-setting" the data.
What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be used? I do not want to manually create and maintain truncate table statements for each table- I would prefer it to be dynamic.
This is one way to do it... there are likely 10 others that are better/more efficient, but it sounds like this is done very infrequently, so here goes...
get a list of the tables from sysobjects, then loop over those with a cursor, calling sp_execsql('truncate table ' + @table_name) for each iteration.
Truncating all of the tables will only work if you don't have any foreign key relationships between your tables, as SQL Server will not allow you to truncate a table with a foreign key.
An alternative to this is to determine the tables with foreign keys and delete from these first, you can then truncate the tables without foreign keys afterwards.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 for further details.
When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"
-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
More on disabling constraints and triggers here
if some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"
Note that the behaviour of RESEED differs between brand new table, and one which had had some data inserted previously from BOL:
DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)
The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.
Thanks to Robert for pointing out the fact that disabling constraints does not allow to use truncate, the constraints would have to be dropped, and then recreated
Don't do this! Really, not a good idea.
If you know which tables you want to truncate, create a stored procedure which truncates them. You can fix the order to avoid foreign key problems.
If you really want to truncate them all (so you can BCP load them for example) you would be just as quick to drop the database and create a new one from scratch, which would have the additional benefit that you know exactly where you are.
I do not see why clearing data would be better than a script to drop and re-create each table.
That or keep a back up of your empty DB and restore it over old one
An alternative option I like to use with MSSQL Server Deveploper or Enterprise is to create a snapshot of the database immediately after creating the empty schema. At that point you can just keep restoring the database back to the snapshot.
It is much easier (and possibly even faster) to script out your database, then just drop and create it from the script.
Make an empty "template" database, take a full backup. When you need to refresh, just restore using WITH REPLACE. Fast, simple, bulletproof. And if a couple tables here or there need some base data(e.g. config information, or just basic information that makes your app run) it handles that too.