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.
Best Answer
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
More on disabling constraints and triggers here
if some of the tables have identity columns we may want to reseed them
Note that the behaviour of RESEED differs between brand new table, and one which had had some data inserted previously from BOL:
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