需要使用单个脚本截断冒险作品2008 / r2中的表 [英] Need to truncate tables in adventure works 2008 /r2 using single script
问题描述
大家好，
我需要使用脚本或命令一次性截断advwrks2008 / r2中的所有表格。
我试过：
- 禁用所有约束
EXEC sp_msforeachtableALTER TABLE？ NOCHECK CONSTRAINT所有
- 删除所有表格中的数据
EXEC sp_MSForEachTableDELETE FROM？
- 启用所有约束
EXEC sp_msforeachtableALTER TABLE？带有CHECK CHECK CONSTRAINT所有
- 某些表格有我们可能想要重新定位的标识列
EXEC sp_MSforeachtableDBCC CHECKIDENT （'？'，RESEED，0）
它只执行一半上升并显示许多错误：
Msg 1934，Level 16，状态1，第1行
DELETE失败，因为以下SET选项的设置不正确：
DELETE失败，因为以下SET选项的设置不正确：'QUOTED_IDENTIFIER'。验证SET选项适用于计算列和/或筛选索引和/或查询通知和/或XML数据类型方法和/或空间索引操作的索引视图和/或索引。
供应商无法删除。它们只能被标记为无效。
消息3609，等级16，状态1，行1
交易在触发器中结束。已经流产了。
请帮帮我。尽快寻求帮助。
问候
Abhishek
Hi Everyone,
I have a need to truncate all tables in advwrks2008 /r2 in a single go using scripts or commands.
I have tried :
"
-- 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"
--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)""
it executes only uptil halfway and shows many errors :
"Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings:
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Vendors cannot be deleted. They can only be marked as not active.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted."
Please help me out. Seeking help asap.
Regards
Abhishek
推荐答案
EXEC sp_MSForEachTable' TRUNCATE TABLE？'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
这篇关于需要使用单个脚本截断冒险作品2008 / r2中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
