删除SQL Server数据库中的所有数据

How I can delete all records from all tables of my database? Can I do it with one SQL command or I need for one SQL command per one table?

如何删除数据库中所有表的所有记录?我可以只使用一个SQL命令，还是每个表需要一个SQL命令?

8 个解决方案

#1

SQLMenace's solution worked for me with a slight tweak to how data is deleted - DELETE FROM instead of TRUNCATE.

sqlthreat的解决方案对我起了作用，它对数据的删除方式进行了微调——删除而不是截断。

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

#2

Usually I will just use the undocumented proc sp_MSForEachTable

通常我只使用没有文档说明的proc sp_MSForEachTable

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

See also: Delete all data in database (when you have FKs)

请参见:删除数据库中的所有数据(当您有FKs时)

#3

I'm aware this is late, but I agree with AlexKuznetsov's suggestion to script the database, rather than going through the hassle of purging the data from the tables. If the TRUNCATE solution will not work, and you happen to have a large amount of data, issuing (logged) DELETE statements might take a long time, and you'll be left with identifiers that have not been reseeded (i.e. an INSERT statement into a table with an IDENTITY column would get you an ID of 50000 instead of an ID of 1).

我知道这已经晚了，但我同意AlexKuznetsov的建议，为数据库编写脚本，而不是从表中清除数据。如果截断的解决方案将不工作,你有一个大的数据量,发行(记录)删除语句可能会需要很长时间,和你会剩下标识符未受移植者(即表的INSERT语句将让你一个ID标识列的50000而不是ID 1)。

To script a whole database, in SSMS, right-click the database, then select TASKS -> Generate scripts:

要编写整个数据库的脚本，在SSMS中，右键单击数据库，然后选择TASKS ->生成脚本:

Click Next to skip the Wizard opening screen, and then select which objects you want to script:

单击Next跳过向导打开屏幕，然后选择要编写脚本的对象:

In the Set scripting options screen, you can pick settings for the scripting, like whether to generate 1 script for all the objects, or separate scripts for the individual objects, and whether to save the file in Unicode or ANSI:

在Set script options屏幕上，您可以选择脚本的设置，比如是否为所有对象生成一个脚本，还是为单个对象生成单独的脚本，以及是否将文件保存为Unicode或ANSI:

The wizard will show a summary, which you can use to verify everything is as desired, and close by clicking on 'Finish'.

向导将显示一个摘要，您可以使用它来验证一切是否符合要求，然后单击“Finish”关闭。

#4

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

#5

It is usually much faster to script out all the objects in the database, and create an empty one, that to delete from or truncate tables.

通常要更快地将数据库中的所有对象编写出来，并创建一个空的对象，以删除或截断表。

#6

1. First you'll have to disable all the triggers :

   首先，你必须禁用所有触发器:

   sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
   

2. Run this script : (Taken from this post Thank you @SQLMenace)

   运行这个脚本:(摘自本文感谢@ sqlthreat)

   SET NOCOUNT ON
   GO
   
   SELECT 'USE [' + db_name() +']';
   ;WITH a AS 
   (
        SELECT 0 AS lvl, 
               t.object_id AS tblID 
        FROM sys.TABLES t
        WHERE t.is_ms_shipped = 0
          AND t.object_id NOT IN (SELECT f.referenced_object_id 
                                  FROM sys.foreign_keys f)
   
        UNION ALL
   
        SELECT a.lvl + 1 AS lvl, 
               f.referenced_object_id AS tblId
        FROM a
        INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id 
                                      AND a.tblID <> f.referenced_object_id
   )
   SELECT 
       'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' 
   FROM a
   GROUP BY tblId 
   ORDER BY MAX(lvl),1
   

This script will produce DELETE statements in proper order. starting from referenced tables then referencing ones

这个脚本将以正确的顺序生成删除语句。从引用表开始，然后引用表

3. Copy the DELETE FROM statements and run them once

   从语句中复制DELETE并运行它们一次

4. enable triggers

   使触发器

   sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
   

5. Commit the changes :

   提交更改:

   begin transaction
   commit;
   

#7

Below a script that I used to remove all data from an SQL Server database

下面是我用来从SQL服务器数据库中删除所有数据的脚本

------------------------------------------------------------
/* Use database */ 
-------------------------------------------------------------

use somedatabase;

GO

------------------------------------------------------------------
/* Script to delete an repopulate the base [init database] */
------------------------------------------------------------------

-------------------------------------------------------------
/* Procedure delete all constraints */ 
-------------------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllConstraints
GO

CREATE PROCEDURE sp_DeleteAllConstraints
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

-----------------------------------------------------
/* Procedure delete all data from the database */ 
-----------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllData' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllData
GO

CREATE PROCEDURE sp_DeleteAllData
AS
    EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

-----------------------------------------------
/* Procedure enable all constraints */ 
-----------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_EnableAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_EnableAllConstraints
GO
-- ....
-- ....
-- ....

#8

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