How to delete all data in SQL Server

A lot of dirty data is inserted in the database, and it is like a haystack if you want to find and delete it one by one. When the useful data of a database is backed up, you can first Delete the data of all tables to make it a database with only a framework, and then restore the backup data.

How can we delete all the data? When deleting tables one by one, you must encounter various troubles such as foreign key constraints and triggers. Obviously, you can remove the foreign key constraint and trigger before deleting the table. See the following SQL statement.

 
Exec sp_msforeachtable 'alter table? Nocheck constraint all 'exec sp_msforeachtable 'alter table? Disable trigger all 'exec sp_msforeachtable 'Delete from? 'Exec sp_msforeachtable' alter table? Check constraint all 'exec sp_msforeachtable 'alter table? Enable trigger all'
 

Sp_msforeachtable is a stored procedure that has never been made public by the Microsoft System database, and sp_msforeachdb is another stored procedure.

The first and second statements above are used to unbind and disable triggers, the third statement is to delete data from all data tables, and the fourth and fifth statements are the recovery constraints and triggers.

Write the preceding statement as a system stored procedure.

Use master
Create procedure sp_deletealldata
As
Exec sp_msforeachtable 'alter table? Nocheck constraint all'
Exec sp_msforeachtable 'alter table? Disable trigger all'
Exec sp_msforeachtable 'delete from? '
Exec sp_msforeachtable 'alter table? Check constraint all'
Exec sp_msforeachtable 'alter table? Enable trigger all'
Go

References:

1. http://www.knowsky.com/541311.html

2. http://hi.baidu.com/windy8848/blog/item/f814b57e25fb77300cd7daef.html