删除 SQLServer 数据库中的所有数据

如何从数据库的所有表中删除所有记录？我可以用一个 SQL 命令做这件事吗? 或者我需要为每个表使用一个 SQL 命令？

254667 次浏览
小开

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

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

小开

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

小开
最佳答案

对于我来说，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
小开 
/* 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
小开

我知道这有点晚了，但是我同意 AlexKuznetsov 的建议，为数据库编写脚本，而不是从表中清除数据。如果 TRUNCATE解决方案不能正常工作，并且您碰巧有大量数据，那么发出(记录的) DELETE语句可能会花费很长时间，并且您将留下未被重新种植的标识符(例如，将 INSERT语句放入具有 IDENTITY列的表中将得到 ID 为50000而不是 ID 为1)。

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

enter image description here

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

enter image description here

在 Set scripting options屏幕上，您可以选择脚本的设置，比如是否为所有对象生成1个脚本，或者为单个对象分离脚本，以及是否用 Unicode 或 ANSI 保存文件:

enter image description here

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

小开

下面是我用来从 SQLServer 数据库中删除所有数据的脚本

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

  1. 首先，你必须关闭所有的触发器:

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

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

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

  1. Copy the DELETE FROM statements and run them once

  2. enable triggers 

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

  3. Commit the changes : 

    begin transaction
commit;
小开 
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
小开

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

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

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

小开

为自己节省一些时间/空间，尽可能使用 TRUNCATE 代替 DELETE, 不会膨胀您的日志文件的情况下，您有一个巨大的数据库。

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
ELSE
TRUNCATE TABLE ?'


EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
小开

结合现有答案中的所有优秀技巧以及更多的技巧，我已经把这个脚本放在一起。它更全面一些，应该更有效率。

删除步骤之前的脚本:

  • 禁用触发器(如果您有任何要删除的触发器，这一点尤其重要) 触发器)
  • 禁用约束检查
  • 禁用非聚集索引(否则它们将逐行被删除) 桌子)

对于删除步骤，它会在可能的地方截断(快得多) ，或者使用表格提示进行删除(表格只对堆表有帮助，而且因为我们缩小了文件，所以它可能不会在这里添加任何内容)。

在删除步骤之后，脚本将:

  • 重新构建/启用所有索引(无论如何都是空的)
  • 启用约束检查
  • 启动触发器
  • 重新播种身份栏
  • 缩小数据库文件

从数据库中删除所有数据:

use [your_database]
go


exec sp_MSforeachtable 'alter table ? disable trigger all'
go


exec sp_MSforeachtable 'alter table ? nocheck constraint all'
go


-- Disable enabled nonclustered indices
declare @script nvarchar(max)
declare cr cursor fast_forward read_only for
select 'alter index ' + quotename(i.name) + ' on ' + quotename(schema_name(t.schema_id))+'.'+ quotename(t.name) + ' disable'
from sys.indexes i inner join sys.tables t on i.object_id = t.object_id
where i.type_desc = 'nonclustered' and i.name is not null and i.is_disabled = 0;
open cr
fetch next from cr into @script
while @@fetch_status = 0
begin
execute sp_executesql @script
fetch next from cr into @script
end
close cr
deallocate cr
go


exec sp_MSforeachtable 'set quoted_identifier on; if objectproperty(object_id(''?''), ''TableHasForeignRef'') = 1 delete from ? with (tablock) else truncate table ?'
go


exec sp_MSforeachtable 'set quoted_identifier on; alter index all on ? rebuild';
go


exec sp_MSforeachtable 'alter table ? with check check constraint all'
go


exec sp_MSforeachtable 'alter table ? enable trigger all'
go


-- Re-seed identity columns
exec sp_MSforeachtable 'if objectproperty(object_id(''?''), ''TableHasIdentity'') = 1 dbcc checkident(''?'', reseed, 0)'
go


-- Shrink the database files
declare @db_name nvarchar(200) = db_name()
dbcc shrinkdatabase (@db_name, 0);
go
小开

EXEC sys.sp _ msforeachtable‘ ALTER TABLE? NOCHECK CONSTRINT ALL’ EXEC sys.sp _ msforeachtable‘ DELETE FROM?’

EXEC sp _ MSForEachTable‘ SET QUOTED _ IDENTIFIER ON; DELETE FROM?’

EXEC sys.sp _ MSForEachTable‘ ALTER TABLE? 检查 CONSTRAINT ALL’


提交答案