如何使用TSQL截断数据库中的所有表？

我有一个数据库的测试环境,我想在测试周期开始时用新数据重新加载.我对重建整个数据库不感兴趣 - 只需简单地"重新设置"数据.

使用TSQL从所有表中删除所有数据的最佳方法是什么？是否有可以使用的系统存储过程,视图等？我不想为每个表手动创建和维护truncate table语句 - 我希望它是动态的.

1> kristof..：

---

当处理从具有外键关系的表中删除数据时 - 基本上是任何设计合理的数据库的情况 - 我们可以禁用所有约束,删除所有数据然后重新启用约束

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

有关禁用约束和触发器的更多信息

如果某些表具有标识列,我们可能需要重新设置它们

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

请注意,RESEED的行为在全新表和之前从BOL插入的数据之间有所不同:

DBCC CHECKIDENT('table_name',RESEED,newReseedValue)

当前标识值设置为newReseedValue.如果自创建表以来没有向表插入任何行,则在执行DBCC CHECKIDENT后插入的第一行将使用newReseedValue作为标识.否则,插入的下一行将使用newReseedValue + 1.如果newReseedValue的值小于标识列中的最大值,则将在对该表的后续引用上生成错误消息2627.

感谢Robert指出禁用约束不允许使用truncate的事实,必须删除约束,然后重新创建

---

禁用约束将不允许截断由FOREIGN KEY约束引用的表.必须删除FK约束.如果我错了,请回复,但我发现没有办法避免丢弃它们.

---

如果您使用的是2008或更新的SSMS,您可能希望在脚本开头添加"SET ROWCOUNT 0",因为默认情况下将操作限制为500行!你会像我一样得到令人沮丧的错误,因为并不是所有数据都会被删除.

---

我最喜欢的答案。但是，为什么您（甚至所有评论者）都将文字SQL字符串括在双引号中？

2> Gulzar Nazim..：

---

对于SQL 2005,

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

结合2000和2005/2008的更多链接..

---

您不能截断具有外键的表,因此只有在表之间没有外键约束(或者它们已被禁用)时才会起作用.

---

@Sam:不,不会!表中的数据无关紧要.只要存在引用该表的外键约束(即使是禁用的表),您也不能截断它.

---

只是继续运行它,它最终会工作:)

---

'EXEC sp_MSForEachTable'DROP TABLE？' 工作得很好:)(从数据库中取悦所有表)

3> Chris KL..：

---

这是数据库擦除脚本的国王.它将清除所有表并正确重新设置它们:

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;

享受,但要小心!

---

不幸的是,如果你有计算列,上面的命令就会失败,因为sp_MSforeachtable显然在它的主体中有`SET QUOTED_IDENTITY OFF`([link](http://spicyjoe.blogspot.ca/2012/01/delete-contents-of-tables). HTML)).更新:修复是添加"SET QUOTED_IDENTIFIERS on;" 到引发此错误的每个语句的开头(如[here](http://markeboyle.wordpress.com/2012/07/04/beware-the-sp_msforeachtable-sets-quoted_identifier-off/))

4> Captain Kenp..：

---

最简单的方法是

打开SQL Management Studio

导航到您的数据库

右键单击并选择Tasks-> Generate Scripts(pic 1)

在"选择对象"屏幕上,选择"选择特定对象"选项并选中"表格"(图2)

在下一个屏幕上,选择"高级",然后将"脚本删除和创建"选项更改为"脚本删除和创建"(图3)

选择将脚本保存到新的编辑器窗口或文件,然后根据需要运行.

这将为您提供一个脚本,可以删除并重新创建所有表,而无需担心调试或是否包含所有内容.虽然这不仅仅是截断,但结果是相同的.请记住,自动递增主键将从0开始,而截断表将记住最后分配的值.如果您无法访问PreProd或Production环境中的Management studio,也可以从代码执行此操作.

1.

2.

3.

---

这是一个完美的方式,谢谢!

5> marcj..：

---

截断所有表只有在表之间没有任何外键关系时才会起作用,因为SQL Server不允许您使用外键截断表.

另一种方法是使用外键确定表并从中删除,然后可以在没有外键的情况下截断表.

有关详细信息,请参阅http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341和http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957.

6> Chris Chilve..：

---

我喜欢与MSSQL Server Deveploper或Enterprise一起使用的另一种选择是在创建空模式后立即创建数据库的快照.此时,您可以继续将数据库还原到快照.

7> 小智..：

---

不要这样做!真的,不是一个好主意.

如果您知道要截断哪些表,请创建一个截断它们的存储过程.您可以修复订单以避免外键问题.

如果你真的想要截断它们(例如你可以BCP加载它们),那么你可以快速删除数据库并从头开始创建一个新的数据库,这将带来额外的好处,你可以确切地知道它在哪里.

---

您的方法的问题是删除表和数据库将导致丢失给予不同登录和模式的所有权限.要重新创建,对于包含大量表的大型数据库来说,这将是一件痛苦的事.