Emptying records from all data tables in SQL Server

Emptying records from all data tables in SQL Server

Clear all records in the data table:

The code is as follows: exec sp_msforeachtable @Command1 = ' truncate table? '
Delete all data tables:

The code is as follows: Exec sp_msforeachtable ' delete N '? '
Method of emptying all table data in a SQL Server database (constrained case)

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.

The code is as follows:
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.

Third method: TRUNCATE TABLE

Quickly delete records in a SQL Server database, emptying the table to delete all rows in the table, the TRUNCATE table statement is a fast, no-logging method. The TRUNCATE TABLE is functionally identical to the DELETE statement that does not contain a WHERE clause. However, TRUNCATE TABLE is faster and uses fewer system resources and transaction log resources.

TRUNCATE TABLE has the following advantages over the DELETE statement:

Less transaction log space is used.

The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and only the page release is recorded in the transaction log.

The locks used are usually less.

When you execute a DELETE statement by using a row lock, the rows in the table are locked for deletion. TRUNCATE table always locks tables and pages instead of locking rows.

Without exception, no pages will be left in the table.

After the DELETE statement is executed, the table will still contain empty pages. For example, you must use at least one exclusive (lck_m_x) table lock to free up empty tables in the heap. If you do not use a table lock when you perform a delete operation, many empty pages will be included in the table (heap). For indexes, the delete leaves some empty pages, although these pages are released quickly through the background cleanup process.

As with the DELETE statement, the definition of a table emptied with TRUNCATE table is persisted with its index and other associated objects in the database.

Emptying records from all data tables in SQL Server