How do you truncate all tables in a database using

Question

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.

Answer 1

Before truncating the tables you have to remove all foreign keys. Use this script to generate final scripts to drop and recreate all foreign keys in database. Please set the @action variable to 'CREATE' or 'DROP'.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

select 'delete from ' +TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'

where result come.

Copy and paste on query window and run the command