The simplest way to delete all data in SQL Server

[51CTO.com exclusive Article] In fact, it is not complicated to delete the data in the database. Why should I do this? First, I will introduce how to delete all the data in the database, because the data may form a constrained relationship between each other, the delete operation may be in an endless loop. Second, the sp_MSForEachTable stored procedure not officially published by Microsoft is used.

Many readers may have experienced this: to clean up an empty database on the basis of the development database, but due to lack of overall understanding of the database structure, you cannot delete the records of a table, due to foreign key constraints, a common database structure is a primary table and a sub-table. In this case, you usually have to delete the sub-Table record before deleting the primary table record.
The delete and truncate statements often come to mind immediately when deleting data records. However, if there are constraints between two or more tables, these two statements may become invalid, in addition, the two commands can only operate on one table at a time. What should I do if I want to delete all records in the SQL Server database? There are two options:

1. delete tables one by one in sequence. This method is unrealistic when there are many tables, even if the number of tables is small, but there are many constraints, you still need to spend a lot of time and energy studying the constraints between them, and then find out which table to delete first, which table to delete, and finally which table to delete.

2. disable all constraints, delete all data, and then enable the constraints. In this way, you do not have to spend time and effort studying any constraints, you only need to write a simple stored procedure to automatically complete this task.
From the two choices, it is easy to see that the second choice is the simplest and most effective. How can we implement the second choice?

First, you have to write code to check all the tables. Here I recommend a stored procedure sp_MSForEachTable, because this stored procedure is not described in Microsoft's official documentation, many developers may have never heard of it, so most of the solutions you can find on the Internet are complicated. Some may think that since there is no official document, this stored procedure may be unstable and will be rejected psychologically, but this is not the case. Let's take a look at a complete script:

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' EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?' GO

This script creates a stored procedure named sp_DeleteAllData. The constraints and triggers are disabled in the first two rows, and the third statement is to delete all data, the next statement restores the constraints and triggers respectively. The last statement is to display the records in each table. Of course, this statement is optional. I just want to check whether all the tables are cleared.

You can run this stored procedure on any database. Of course, you should not run it on the generated database. Don't blame me for not telling you! In any case, back up the database, use the backup database to restore the database, and then run the stored procedure. Haha, even a large database does not take long, your database becomes an empty database, and you are a little scared!

Link: http://hi.baidu.com/hopeforeverwyj/blog/item/06569152a8347a060cf3e3db.html