The simplest way to delete all data in SQL Server

Original: The simplest way to delete all data in SQL Server

The simplest Way to delete all data in SQL Server

Author : CC Abba

2014-3-14

in fact, the method of deleting the data in the database is not complicated, why do I have to superfluous it, 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 it comes to deleting data records, it's often immediately conceivable thatDeleteand thetruncatestatement, but if there are constraints between two or more tables, both statements may fail, and most of all, both commands can only operate one table at a time. Then really encounter to deleteSQL ServerWhat happens when all the records are in the database? There are two options:

1. in order to delete in sequence, this method is very unrealistic in the case of very many tables, even if the number of tables is not many, but the constraints are relatively 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 to delete first, then delete which table, and finally delete which table.

2. Disable all constraints, delete all data, and finally enable the constraint so that you don't have to spend time and effort studying what constraints Just write a simple stored procedure to automate this task.
from these two choices it is easy to see that the second option is the simplest and most effective, and when you use the second option, How to implement it concretely?

First you have to write code to loop through 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 not have heard of, so your search on the Internet, the solution is mostly very complex, perhaps some people think, since there is no official documents, the stored procedure may be unstable, psychological will exclude it, but it is not true. Let's look at a complete script first:

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

The case should be separated.

This script creates a name for the Sp_deletealldata stored procedures, the first two lines of statements disable constraints and triggers, the third statement is 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 BACKUP database Restore, and then run the stored procedure, hehe, even a large database, or how long, your database becomes an empty library, a little afraid of the feeling!

based on the above, we have sp_msforeachtable with the Sp_msforeachdb The origins of these two SPS

in theMSSQLThere are many non-public system stored procedures, which may be commonly usedsp_msforeachtableand theSp_msforeachdbhave this2A.
each user table, each database that is used to traverse a database, respectively.