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

Delete all data in SQL Server database

提问by AndreyAkinshin

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 命令？

回答by Ryan Kirkman

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

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

回答by SQLMenace

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)

另请参阅：删除数据库中的所有数据（当您有 FK 时）

回答by Harpal

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

回答by SchmitzIT

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 TRUNCATEsolution will not work, and you happen to have a large amount of data, issuing (logged) DELETEstatements might take a long time, and you'll be left with identifiers that have not been reseeded (i.e. an INSERTstatement into a table with an IDENTITYcolumn would get you an ID of 50000 instead of an ID of 1).

我知道这已经晚了，但我同意 AlexKuznetsov 的建议，即编写数据库脚本，而不是从表中清除数据。如果TRUNCATE解决方案不起作用，并且您碰巧有大量数据，则发布（记录）DELETE语句可能需要很长时间，并且您将留下尚未重新播种的标识符（即，将INSERT语句放入带有一IDENTITY列将为您提供 50000 的 ID，而不是 1 的 ID）。

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

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

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

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

In the Set scripting optionsscreen, 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 scripting options屏幕中，您可以选择脚本的设置，例如是为所有对象生成 1 个脚本，还是为单个对象生成单独的脚本，以及是否以 Unicode 或 ANSI 保存文件：

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

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

回答by mounaim

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

   sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
   

2. Run this script : (Taken from this postThank 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
   

1. 首先，您必须禁用所有触发器：

   sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
   

2. 运行这个脚本：（取自这篇文章谢谢@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
   

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

此脚本将按DELETE正确顺序生成语句。从引用表开始，然后引用表

1. Copy the DELETE FROMstatements and run them once

2. enable triggers

   sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
   

3. Commit the changes :

   begin transaction
   commit;
   

3. 复制DELETE FROM语句并运行一次

4. 启用触发器

   sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
   

5. 提交更改：

   begin transaction
   commit;
   

回答by A-K

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.

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

回答by benahm

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

在我用来从 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
-- ....
-- ....
-- ....

回答by mikem

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

回答by dmoore1181

As an alternative answer, if you Visual Studio SSDT or possibly Red Gate Sql Compare, you could simply run a schema comparison, script it out, drop the old database (possibly make a backup first in case there would be a reason that you will need that data), and then create a new database with the script created by the comparison tool. While on a very small database this may be more work, on a very large database it will be much quicker to simply drop the database then to deal with the different triggers and constraints that may be on the database.

作为替代答案，如果您使用 Visual Studio SSDT 或可能使用 Red Gate Sql Compare，您可以简单地运行模式比较，编写脚本，删除旧数据库（可能首先进行备份，以防万一您需要该数据），然后使用比较工具创建的脚本创建一个新数据库。虽然在一个非常小的数据库上这可能需要更多的工作，但在一个非常大的数据库上，简单地删除数据库然后处理数据库上可能存在的不同触发器和约束会更快。

回答by Buddhika De Silva

Yes, it is possible to delete with a single line of code

是的，可以用一行代码删除

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'