我有一个数据库测试环境,我想在测试周期开始时重新加载新数据。我对重建整个数据库不感兴趣——只是简单地“重新设置”数据。
使用 TSQL 从所有表中删除所有数据的最佳方法是什么?有没有可以使用的系统存储过程、视图等?我不想为每个表手动创建和维护截断表语句——我希望它是动态的。
在处理从具有外键关系的表中删除数据时——这基本上是任何设计合理的数据库的情况——我们可以禁用所有约束,删除所有数据,然后重新启用约束
-- disable all constraints
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"
-- enable all constraints
exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
有关禁用约束和触发器的更多信息here
如果某些表有标识列,我们可能想要重新设置它们
EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"
请注意,在全新表和之前从 BOL 插入一些数据的表之间,RESEED 的行为有所不同:
DBCC CHECKIDENT ('table_name', RESEED, newReseedValue) 当前标识值设置为 newReseedValue。如果自创建以来没有向表中插入任何行,则执行 DBCC CHECKIDENT 后插入的第一行将使用 newReseedValue 作为标识。否则,插入的下一行将使用 newReseedValue + 1。如果 newReseedValue 的值小于标识列中的最大值,则在后续引用该表时将生成错误消息 2627。
感谢 Robert 指出禁用约束不允许使用截断这一事实,必须删除约束,然后重新创建
对于 SQL 2005,
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
这是数据库擦除脚本之王。它将清除所有表并正确重新设置它们:
SET QUOTED_IDENTIFIER ON;
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON';
IF NOT EXISTS (
SELECT
*
FROM
SYS.IDENTITY_COLUMNS
JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
WHERE
SYS.TABLES.Object_ID = OBJECT_ID('?') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID('?'), 'TableHasIdentity' ) = 1
DBCC CHECKIDENT ('?', RESEED, 0) WITH NO_INFOMSGS;
享受,但要小心!
最简单的方法是
打开 SQL Management Studio 导航到您的数据库 右键单击并选择 Tasks->Generate Scripts(图 1) 在“选择对象”屏幕上,选择“选择特定对象”选项并检查“表”(图 2)下一个屏幕,选择“高级”,然后将“脚本删除和创建”选项更改为“脚本删除和创建”(图 3)选择将脚本保存到新的编辑器窗口或文件并根据需要运行。
这将为您提供一个脚本,该脚本可以删除并重新创建所有表,而无需担心调试或是否包含所有内容。虽然这不仅仅是截断,但结果是相同的。请记住,您的自动递增主键将从 0 开始,而不是截断表,它会记住最后分配的值。如果您无权访问 PreProd 或生产环境中的 Management Studio,您也可以从代码中执行此操作。
1.
https://i.stack.imgur.com/KWXSP.png
2.
https://i.stack.imgur.com/fwUxD.png
3.
https://i.stack.imgur.com/Jq2E0.png
仅当您的表之间没有任何外键关系时,截断所有表才有效,因为 SQL Server 不允许您截断具有外键的表。
另一种方法是确定具有外键的表并首先从中删除,然后您可以在之后截断没有外键的表。
有关详细信息,请参阅 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 和 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957。
不要这样做!真的,不是一个好主意。
如果您知道要截断哪些表,请创建一个存储过程来截断它们。您可以修复订单以避免外键问题。
如果您真的想将它们全部截断(例如,您可以 BCP 加载它们),您将同样快速地删除数据库并从头开始创建一个新数据库,这将具有额外的好处,即您确切地知道自己在哪里。
我喜欢与 MSSQL Server Developer 或 Enterprise 一起使用的另一个选项是在创建空模式后立即创建数据库的快照。此时,您可以继续将数据库恢复到快照。
如果您想将数据保留在特定表(即静态查找表)中,同时删除/截断同一数据库中其他表中的数据,那么您需要一个包含异常的循环。当我偶然发现这个问题时,这就是我正在寻找的东西。
sp_MSForEachTable 对我来说似乎有问题(即与 IF 语句的行为不一致),这可能是 MS 未记录它的原因。
declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)
if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails'))
begin
exec('truncate table [' + @TableName + ']')
end
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end
截断所有表最困难的部分是删除和重新添加外键约束。
以下查询为与@myTempTable 中的每个表名相关的每个约束创建 drop 和 create 语句。如果您想为所有表生成这些,您可以简单地使用信息模式来收集这些表名。
DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
('TABLE_ONE'),
('TABLE_TWO'),
('TABLE_THREE')
-- DROP FK Contraints
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
'.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name)
FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
WHERE fk.referenced_object_id IN
(
SELECT so.object_id
FROM sys.objects so JOIN sys.schemas sc
ON so.schema_id = sc.schema_id
WHERE so.name IN (SELECT * FROM @myTempTable) AND sc.name=N'dbo' AND type in (N'U'))
-- CREATE FK Contraints
SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +'])
REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])'
FROM sysobjects f
INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
INNER JOIN sys.sysreferences r ON f.id = r.constid
INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE
f.type = 'F'
AND
cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)
然后我只需复制要运行的语句 - 但通过一些开发工作,您可以使用游标动态运行它们。
编写数据库脚本要容易得多(甚至可能更快),然后从脚本中删除并创建它。
制作一个空的“模板”数据库,进行完整备份。当您需要刷新时,只需使用 WITH REPLACE 恢复即可。快速,简单,防弹。如果这里或那里的几个表需要一些基本数据(例如配置信息,或者只是使您的应用程序运行的基本信息),它也会处理。
这是一种方法......可能还有其他 10 种更好/更有效的方法,但听起来这种情况很少发生,所以这里......
从 sysobjects
中获取 tables
的列表,然后用光标遍历这些列表,为每个 iteration
调用 sp_execsql('truncate table ' + @table_name)
。
运行一次注释掉的部分,用您想要截断的表填充 _TruncateList 表,然后运行脚本的其余部分。如果您经常这样做,则需要随着时间的推移清理 _ScriptLog 表。
如果你想要做所有的表,你可以修改它,只需输入 SELECT name INTO #TruncateList FROM sys.tables。但是,您通常不想全部完成。
此外,这将影响数据库中的所有外键,如果它对您的应用程序来说过于生硬,您也可以对其进行修改。这不是为了我的目的。
/*
CREATE TABLE _ScriptLog
(
ID Int NOT NULL Identity(1,1)
, DateAdded DateTime2 NOT NULL DEFAULT GetDate()
, Script NVarChar(4000) NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
DateAdded
, ID
)
CREATE TABLE _TruncateList
(
TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN
DROP TABLE #DropFK
END
IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
DROP TABLE #TruncateList
END
IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
DROP TABLE #CreateFK
END
SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP CONSTRAINT ' + '[' + f.name + ']'
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
SELECT TableName
INTO #TruncateList
FROM _TruncateList
SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO #CreateFK
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK
DECLARE @Cmd NVarChar(4000)
, @TableName SysName
WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
SELECT TOP 1 @Cmd = Scripts
FROM #DropFK
EXEC (@Cmd)
DELETE #DropFK WHERE Scripts = @Cmd
END
WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' + TableName
, @TableName = TableName
FROM #TruncateList
EXEC (@Cmd)
DELETE #TruncateList WHERE TableName = @TableName
END
WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
SELECT TOP 1 @Cmd = Scripts
FROM #CreateFK
EXEC (@Cmd)
DELETE #CreateFK WHERE Scripts = @Cmd
END
这有点晚了,但它可能会帮助某人。我有时会创建一个过程,它使用 T-SQL 执行以下操作:
将所有约束存储在临时表中 删除所有约束 截断所有表,但某些不需要截断的表除外 重新创建所有约束。
我在我的博客上列出了它here
我不明白为什么清除数据比删除和重新创建每个表的脚本更好。
或者备份您的空数据库并将其恢复到旧数据库
select 'delete from ' +TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
结果从何而来。
复制并粘贴到查询窗口并运行命令
SET ROWCOUNT 0
,因为默认设置是将操作限制为 500 行!您会像我一样遇到令人沮丧的错误,因为并非所有数据都已被实际删除。