Clear records in all data tables in SQL Server

Clear records in all data tables in SQL Server

Clear records in all data tables:

Copy codeThe Code is as follows: exec sp_msforeachtable @ Command1 = 'truncate table? '

Delete all data tables:

Copy codeThe Code is as follows: exec sp_msforeachtable 'delete n ''? '''

How to clear all table data in the SQL Server database (with constraints)

In fact, the method of deleting data in the database is not complicated. Why do I need to delete all the data in the database? First, I will introduce how to delete all the data in the database, because the data may form a mutually binding relationship, the delete operation may be in an endless loop. Second, the sp_MSForEachTable stored procedure that is not officially published by Microsoft is used.

Many readers may have experienced this: to clean up an empty database on the basis of the development database, but due to lack of overall understanding of the database structure, you cannot delete the records of a table, due to foreign key constraints, a common database structure is a primary table and a sub-table. In this case, you usually have to delete the sub-Table record before deleting the primary table record.

The delete and truncate statements often come to mind immediately when deleting data records. However, if there are constraints between two or more tables, these two statements may become invalid, in addition, the two commands can only operate on one table at a time. What should I do if I want to delete all records in the SQL Server database? There are two options:

1. delete tables one by one in sequence. This method is unrealistic when there are many tables, even if the number of tables is small, but there are many constraints, you still need to spend a lot of time and energy studying the constraints between them, and then find out which table to delete first, which table to delete, and finally which table to delete.

2. disable all constraints, delete all data, and then enable the constraints. In this way, you do not have to spend time and effort studying any constraints, you only need to write a simple stored procedure to automatically complete this task.

Copy codeThe 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

From the two choices, it is easy to see that the second choice is the simplest and most effective. How can we implement the second choice?

First, you have to write code to check 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 have never heard of it, so most of the solutions you can find on the Internet are complicated. Some may think that since there is no official document, this stored procedure may be unstable and will be rejected psychologically, but this is not the case. Let's take a look at a complete script:

This script creates a stored procedure named sp_DeleteAllData. The constraints and triggers are disabled in the first two rows, and the third statement is to delete all data, the next statement restores the constraints and triggers respectively. The last statement is to display the records in each table. Of course, this statement is optional. I just want to check whether all the tables are cleared.

You can run this stored procedure on any database. Of course, you should not run it on the generated database. Don't blame me for not telling you! In any case, back up the database, use the backup database to restore the database, and then run the stored procedure. Haha, even a large database does not take long, your database becomes an empty database.

Method 3: TRUNCATE TABLE

Quickly delete records in the SQL server database. to delete all rows in a TABLE, TRUNCATE TABLE statements are a fast and non-log-free method. The truncate table function is the same as the DELETE statement without the WHERE clause. However, truncate table is faster and uses fewer system resources and transaction log resources.

Compared with the DELETE statement, truncate table has the following advantages:

The transaction log space is small.

The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data page used to store TABLE data, and only records the page release in transaction logs.

The number of locks used is usually small.

When the DELETE statement is executed using the row lock, the rows in the table are locked for deletion. Truncate table always locks tables and pages, rather than locking rows.

Without exceptions, no pages are left in the table.

After the DELETE statement is executed, the table still contains blank pages. For example, you must use at least one exclusive (LCK_M_X) Table lock to release empty tables in the heap. If the table lock is not used during the delete operation, the table (HEAP) contains many blank pages. For indexes, the delete operation leaves some blank pages, although these pages are quickly released through the background clearing process.

Similar to the DELETE statement, the definition of the TABLE cleared using the truncate table is retained in the database together with its index and other associated objects.