The easiest way to remove all the data in SQL Server

In fact, the way to delete the data in the database is not complicated, why do I have to superfluous, I am here to introduce the deletion of the database all the data, because the data may form a mutually binding relationship between the deletion of the operation may fall into a dead loop, and the second is the use of Microsoft's sp_ Msforeachtable stored procedures.

Maybe a lot of readers have been through this kind of thing: to clean up an empty library based on the development database, but because of the lack of a comprehensive understanding of the database structure, when deleting a table record, can not delete, because there may be foreign key constraints, a common database structure is a master table, a child table, In this case, you will generally have to delete the child table records and then delete the primary table records.

When you delete a data record, you tend to think of the delete and TRUNCATE statements immediately, but when you encounter a constraint between two or more tables, both statements may be invalidated, and most of all, the two commands can only operate one table at a time. What happens when you actually encounter all the records in the SQL Server database that you want to delete? There are two options:

1. In order to delete the sequence, this method in the table is very many cases appear very unrealistic, even if the number of tables is not much, but the constraints are more, you still have to spend a lot of time and energy to study the constraints of the relationship, and then find out which table to delete, then delete which table, and then delete which table.

2. Disable all constraints, delete all data, and finally enable constraints, so that you do not have to spend time and effort to study what constraints, you need to write a simple stored procedure can automatically complete this task.

It is easy to see from these two choices that the second option is the simplest and most effective, so how do you implement it when you use the second option?

The first thing to do is to write a code loop to check all the tables, and I recommend a stored procedure sp_msforeachtable, because there is no description of the stored procedure in Microsoft's official documentation, and many developers may not have heard of it, so the solution you find on the Internet is mostly complex, Some people may think that, since there is no official document, the stored procedure may be unstable, psychologically will exclude it, 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, which disables the constraints and triggers in the first two lines, and the third statement really deletes all the data, followed by the statements that restore the constraints and triggers respectively, and the last statement shows the records in each table, Of course this statement can also not, I just want to make sure that all the tables are cleared.

You can run this stored procedure on any database, but don't run it on the build database, but don't blame me for not telling you! Anyway, or back up the database, use BACKUP database Restore, and then run the stored procedure, oh, even a large database, or how long, your database into an empty library, a bit afraid of the feeling!