Deletes data from all tables in the specified database of sqlserver.

In fact, it is not complicated to delete the data in the database. Why should I 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 constrained relationship between each other, the delete operation may be in an endless loop. Second, the sp_msforeachtable stored procedure not officially published by Microsoft is used.
many readers may have experienced this problem: To clean up an empty database on the basis of the development database, the database structure is not fully understood, when deleting a table record, it cannot be deleted because there may be foreign key constraints. A common database structure is a primary table and a sub table, in this case, you generally need to delete the sub-Table record before deleting the master table record.
When deleting a data record, the delete and truncate statements often occur immediately. However, if there are constraints between two or more tables, both statements may be invalid, and 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.
from the two options, it is not difficult to see that the second option is the simplest and most effective. When using the second option, how should we implement it?
first, you must write Code to check all tables cyclically. Here I recommend a stored procedure sp_msforeachtable, because Microsoft does not describe the stored procedure in its official documents, many developers may not have heard of it yet. Therefore, most of the solutions you can find on the Internet are complex, 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. The following is 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 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, and you are a little scared.

 

 

 

 

 

 

 

 

 

 

 

 

Recently, I found that the database is too large and has insufficient space. Therefore, I plan to clear all the data in the database, but there are a lot of tables, one by one, which is really troublesome, therefore, we want to use SQL statements to clear all data at a time. three methods are found for clearing. the database used is ms SQL Server.
1. Search all table names and construct an SQL statement.

Declare   @ Trun_name   Varchar ( 8000 )
Set   @ Trun_name = ''
Select   @ Trun_name = @ Trun_name   +   ' Truncate table '   +   [ Name ]   +   '   '   From Sysobjects Where Xtype = ' U '   And Status >   0
Exec ( @ Trun_name )

This method is applicable when there are not many tables. Otherwise, the number of tables exceeds the length of the string and cannot be completely cleared.
2. Use a cursor to clear all tables

 

Declare   @ Trun_name   Varchar ( 50 )
Declare Name_cursor Cursor   For
Select   ' Truncate table '   + Name From Sysobjects Where Xtype = ' U '   And Status >   0
Open Name_cursor
Fetch   Next   From Name_cursor Into   @ Trun_name
While   @ Fetch_status   =   0
Begin
Exec ( @ Trun_name )
  Print   ' Truncated table '   +   @ Trun_name
  Fetch   Next   From Name_cursor Into   @ Trun_name
End
Close Name_cursor
Deallocate Name_cursor

This is self-built and can be called as a stored procedure. It can clear all the table data at a time, and it can also be selected to clear the table.
3. Use Microsoft's undisclosed stored procedures

Exec Sp_msforeachtable" Truncate   Table ? "