How SQL Server empties all of the database table data

1. Empty all table data in SQL Server database

(1) Write the stored procedure script:

CREATE PROCEDURE Sp_deletealldata
as
EXECsp_msforeachtable ' ALTER TABLE? NOCHECK CONSTRAINT All '
EXEC sp_msforeachtable ' ALTER TABLE? DISABLE TRIGGER All '
EXEC sp_msforeachtable ' DELETE from? '
EXECsp_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 the tables (Microsoft Official documents).

< Span style= "Color:rgb (51,51,51); Font-family:arial;font-size:14px;line-height:26px;background-color:rgb ( 255,255,255); " > The script creates a stored procedure named Sp_deletealldata, with the first two lines disabling constraints and triggers, the third is really deleting all the data, the statements in the next one are restoring the constraints and triggers, and the last statement is showing the records in each table. Confirm that all table data is emptied.

< Span style= "Color:rgb (51,51,51); Font-family:arial;font-size:14px;line-height:26px;background-color:rgb ( 255,255,255); " > (2) query out all tables of the database and delete the table data one by one using the TRUNCATE statement:

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

SELECT name from SysObjects Where xtype= ' U ' ORDER by name

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

Then use the TRUNCATE or DELETE statements to delete the table data individually

TRUNCATE TABLE Order_buyer;

TRUNCATE TABLE Order_seller;

TRUNCATE TABLE receivelist;

...

This article is from the "Sharemi" blog, make sure to keep this source http://sharemi.blog.51cto.com/11703359/1790643

How SQL Server empties all of the database table data