Empty the records in all datasheets in SQL Server _mssql

Empty records in all data tables in SQL Server

To empty records in all data tables:

Copy Code code as follows:

exec sp_msforeachtable @Command1 = ' truncate table? '

Delete all data tables:

Copy Code code as follows:

exec sp_msforeachtable ' delete N '? '

Methods for emptying all table data in a SQL Server database (with constraints)

In fact, the way to delete the data in the database is not complicated, why do I have to superfluous, I am here to introduce the deletion of the database all the data, because the data may form a mutually binding relationship between the deletion of the operation may fall into a dead loop, and the second is the use of Microsoft's sp_ Msforeachtable stored procedures.

Maybe a lot of readers have been through this kind of thing: to clean up an empty library based on the development database, but because of the lack of a comprehensive understanding of the database structure, when deleting a table record, can not delete, because there may be foreign key constraints, a common database structure is a master table, a child table, In this case, you will generally have to delete the child table records and then delete 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 be invalidated, and most of all, the two commands can only operate one table at a time. So what do you do when you actually encounter all the records in the SQL Server database that you want to delete? There are two options:

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

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

Copy Code code 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 easy to see from these two choices that the second option is the simplest and most effective, so how do you implement it when you use the second option?

The first thing to do is to write a code loop to check all the tables, and I recommend a stored procedure sp_msforeachtable, because there is no description of the stored procedure in Microsoft's official documentation, and many developers may not have heard of it, so the solution you find on the Internet is mostly complex, Some people may think that, since there is no official document, the stored procedure may be unstable, psychologically will exclude it, but this is not the case. Let's take a look at a complete script:

This script creates a stored procedure named Sp_deletealldata, which disables the constraints and triggers in the first two lines, and the third statement really deletes all the data, followed by the statements that restore the constraints and triggers respectively, and the last statement shows the records in each table, Of course this statement can also not, I just want to make sure that all the tables are cleared.

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

Third method: TRUNCATE table

quickly delete records in a SQL Server database, empty a table to delete all rows in a table, TRUNCATE A TABLE statement is a quick, logging-free method. The TRUNCATE TABLE is functionally identical to a DELETE statement that does not contain a WHERE clause. However, TRUNCATE TABLE is faster and uses less system resources and transaction log resources.

Compared to the DELETE statement, the TRUNCATE TABLE has the following advantages:

uses less transaction log space. 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 pages used to store table data, and only records page releases in the transaction log. The

typically uses fewer locks.

When a DELETE statement is executed with a row lock, the rows in the table are locked for deletion. TRUNCATE table always locks tables and pages instead of locking each row.

If there are no exceptions, no pages will be left in the table.

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

is the same as the DELETE statement, and the definition of the table emptied with the TRUNCATE table remains in the database with its index and other associated objects.