How SQL Server clears all table data in a database

1. Empty all table data in SQL Server database

(1) write stored procedure scripts:

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

Description:

Stored procedure sp_MSForEachTable: loop through all tables (Microsoft official documentation).

The script creates a stored procedure named sp_DeleteAllData, the first two lines disable constraints and triggers respectively, the third statement actually deletes all data, the next statement restores constraints and triggers, and the last statement displays the records in each table to confirm that all table data has been cleared.

(2) query all the tables in the database and delete the table data one by one using the truncate statement:

First, use the select statement to query all the table names in the database

SELECT name FROM SysObjects Where XType='U' ORDER BY Name

Query all the table names under the default current database. If you need to query the tables under other databases, add the Where condition name = [dbname]

Then delete the table data one by one using truncate or delete statements

Truncate table order_buyer

Truncate table order_seller

Truncate table receivelist

...