-
In fact, the method of deleting data in the database is not complicated. Why should I bother with it? First, what I introduce here is to delete all the data in the database. Because the data may form mutual constraints, the deletion operation may fall into an infinite loop. In fact, the method of deleting data in the database is not complicated. Why should I bother with it? First, what I am introducing here is to delete all the data in the database. Because the data may form mutual constraints, the deletion operation may fall into an infinite loop. Second, Microsoft's unofficial sp_MSForEachTable stored procedure is used here.
Maybe many readers and friends have experienced this kind of thing: they want to clean up an empty database based on the development database, but due to the lack of overall understanding of the database structure, when deleting records in a table, they cannot delete them because there may be foreign key constraints. A common database structure is a main table and a sub-table. In this case, you generally have to delete the sub-table records first, and then delete the main table records.
When it comes to deleting data records, the delete and truncate statements often come to mind. However, if there are constraints between two or more tables, these two statements may fail, and the most terrible thing is these two commands. You can only operate one table at a time. So what should you do when you really want to delete all the records in the SQL Server database? There are two options:
1. Delete one by one in order. This method is very unrealistic when there are a lot of tables. Even if there are not many tables but there are many constraints, you still have to spend a lot of time and energy to study the constraint relationships. Then find out which table to delete first, which table to delete next, and which table to delete last.
2. Disable all constraints, delete all data, and finally enable the constraints. This way you don’t have to spend time and energy studying constraints. You only need to write a simple stored procedure to automatically complete this task.
From these two options, it is not difficult to see that the second option is the simplest and most effective. So when using the second option, how to implement it?
First, you have to write code to loop through all tables. Here I recommend a stored procedure sp_MSForEachTable, because there is no description of this stored procedure in Microsoft's official documentation, and many developers may not have heard of it, so you can search it on the Internet. Most of the solutions are very complicated. Some people may think that since there is no official document, this storage process may be unstable, and they will psychologically reject 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. The first two statements disable constraints and triggers respectively. The third statement actually deletes all data. The following statements restore constraints and triggers respectively. The last statement It displays the records in each table. Of course, this statement can be omitted. I just want to confirm whether all tables have been cleared.
You can run this stored procedure on any database, of course not on the generated database, but don’t blame me for not telling you! Anyway, back up the database first, use the backup database to restore, and then run the stored procedure, haha, even if It is a large database. It will not take long for your database to become an empty database, which is a bit scary!