Ways to empty all table data in a SQL Server database

Original: Method of emptying all table data in a SQL Server database

In fact, the method of deleting the data in the database is not complicated, why do I have to superfluous, one is that I am here to delete all the data from the database, because the data may form a mutual constraint relationship between the deletion operation may be in a dead loop, and the second is the use of Microsoft is not formally disclosed sp_ Msforeachtable stored procedures.

Perhaps a lot of readers have experienced this kind of thing: to clean up an empty library on the basis of the development database, but because of the lack of a general understanding of the database structure, when deleting a table record, delete, because there may be foreign key constraints, a common database structure is a main table, a child table, In this case, it is common to delete the child table records before deleting 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 fail, and most of all, both commands can only manipulate one table at a time. So what do you do when you actually encounter all the records in the SQL Server database? There are two options:

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

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

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

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

First you have to write code to cycle through all the tables, here I recommend a stored procedure sp_msforeachtable, because there is no description of this stored procedure in Microsoft's official documents, many developers may have not heard of, so your search on the Internet The solution is mostly very complex, Some people may think that since there is no official document, the stored procedure may be unstable and psychologically repel it, but that is not the case. Let's look at a complete script here:

This script creates a stored procedure named Sp_deletealldata, the first two lines of statements disable constraints and triggers, the third statement is to really delete all the data, followed by the statement to restore the constraints and triggers, the last statement is to display the records in each table, Of course this statement can also not, I just want to confirm whether to clear all the tables.

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, back up the database, use a BACKUP database restore, and then run the stored procedure, hehe, even a large database, or how long, Your database is going to be an empty vault.

Ways to empty all table data in a SQL Server database