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

Question

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

sqlsql-serverdelete-rowmulti-table-delete

148

如何删除数据库中所有表的所有记录？我需要使用一个 SQL 命令还是每个表都需要一个 SQL 命令？

- AndreyAkinshin

13个回答

39

通常我会使用未记录的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

另请参阅：在拥有外键时删除数据库中的所有数据

- SQLMenace

2

我认为这个不起作用。看起来Kalen Delaney无意中成为了这个想法的发起人。在这里，她澄清道：“您必须删除引用约束才能截断表。”（http://www.eggheadcafe.com/software/aspnet/29927698/cant-truncate-table.aspx） - Martin Smith

Martin，我刚刚在Adventureworks数据库中运行了它，没有任何问题。 - SQLMenace

这在我这里绝对行不通。

create database testing; GO use testing; create table t1 (i int primary key) create table t2(i int primary key,p int references t1)

- Martin Smith

3

这个方法无效，尽管它被标记为答案。在外键上设置nocheck约束不允许您在这些表上运行truncate命令。您仍然会遇到导致无法截断的错误。 - Fourth

4

由于存在外键，这不起作用。但我仍然看不出为什么它被接受为答案 :/ - mounaim

22

/* 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

- Harpal

有趣的脚本，它不使用未记录的存储过程“sp_MSForEachTable”，该存储过程在Azure上缺失。但是，如果您拥有其他模式（而非[dbo]）上的对象，则需要进行调整。 - Pac0

请使用 https://gist.github.com/metaskills/893599 在Azure中创建sp_MSForEachTable。 - Harpal

19

我知道已经晚了，但是我同意AlexKuznetsov的建议，脚本化数据库，而不是费时费力地从表中清除数据。如果TRUNCATE解决方案行不通，并且您恰好有大量数据，发出(已记录的)DELETE语句可能需要很长时间，并且您将留下未重新种植的标识符（即，在具有IDENTITY列的表中插入一个INSERT语句会使您获得50000而不是1的ID）。

要为整个数据库编写脚本，在SSMS中，右键单击数据库，然后选择TASKS -> Generate scripts：

输入图像描述

点击Next跳过向导打开屏幕，然后选择要脚本化的对象：

输入图像描述

在Set scripting options屏幕中，您可以选择脚本的设置，例如生成所有对象的1个脚本，还是单独的对象的分离脚本，以及是否将文件保存为Unicode或ANSI：

输入图像描述

向导将显示摘要，您可以使用它来验证一切是否符合要求，并通过单击“完成”关闭。

- SchmitzIT

1

小心，如果你不点击“高级”按钮，按默认方式操作会丢失索引等内容。 - glautrou

9

First you'll have to disable all the triggers :

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

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

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

这个脚本将产生适当顺序的DELETE语句，从引用表开始，然后是被引用的表。

Copy the DELETE FROM statements and run them once

enable triggers

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

Commit the changes :
```
begin transaction
commit;
```

- mounaim

这对我不起作用，递归查询最终陷入了循环。可能是因为自我引用的原因。 - Edwin Stoteler

5

通常，脚本化数据库中的所有对象并创建一个空的数据库比从表中删除或截断要快得多。

- A-K

3

以下是我用来清空SQL Server数据库中所有数据的脚本：

------------------------------------------------------------
/* 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
-- ....
-- ....
-- ....

- benahm

1

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

- mikem

0

作为另一种解决方案，如果您使用Visual Studio SSDT或可能的Red Gate Sql Compare，您可以简单地运行模式比较，将其脚本化，删除旧数据库（在可能需要该数据的情况下首先进行备份），然后使用比较工具创建的脚本创建新数据库。虽然在非常小的数据库上可能需要更多的工作，但在非常大的数据库上，仅仅删除数据库比处理可能存在于数据库上的不同触发器和约束条件要快得多。

- dmoore1181

0

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sys.sp_msforeachtable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

执行 sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 执行 sys.sp_msforeachtable 'DELETE FROM ?'

执行 sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

执行 sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

- Toqeer Yousaf

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ryan Kirkman · Accepted Answer

226

SQLMenace的解决方案对我有用，只需轻微调整即可删除数据 - 使用DELETE FROM而不是TRUNCATE。

-- 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

- Ryan Kirkman

22

在执行完DELETE FROM操作后，执行EXEC sp_MSForEachTable 'DBCC CHECKIDENT(''?'', RESEED, 0)'可以将所有自增列的值重置为0，这样做也许是有意义的。 - Jonathan Amend

2

当你发现6行代码可以替换数百个删除语句时，这一天就是一个好的开始！这种方法在SQL 2014 Express上运行没有问题。 - Tommy

1

不要忘记禁用触发器。 - Edwin Stoteler

13

我遇到了一个错误 - DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...。以下代码对我有效： EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'。 - kasi

1

在我执行此操作之前，由于对SQL Server不是完全自信，我注意到答案没有任何关于哪个数据库的指示。我的SQL服务器有很多数据库，我怎么能确信它只适用于我想要的数据库？（我肯定不会随便尝试并看看会发生什么！！！） - JimbobTheSailor

显示剩余3条评论