删除所有表时出错“删除失败，因为以下 SET 选项的设置不正确:‘QUOTED_IDENTIFIER’"；

2021-09-10 00:00:00 tsql sql-server

我有一个脚本来删除我的数据库中的所有表，如下所示:

I have a script to delete all tables in my database that looks like this:

-- Disable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Disable all triggers 
EXEC EnableAllTriggers @Enable = 0

-- Delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'
 
-- Dnable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
 
-- Reseed identity columns
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 0)'

-- Enable all triggers
EXEC EnableAllTriggers @Enable = 1

当它碰到 DELETE 行时，我收到一些表的错误:

When it hits the DELETE line I get this error for a few of the tables:

DELETE 失败，因为以下 SET 选项不正确设置:'QUOTED_IDENTIFIER'.验证 SET 选项是否正确与索引视图和/或计算列和/或索引一起使用过滤索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作.

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.

我没有任何索引视图，所有外键和触发器都被禁用，所以我不知道是什么导致了这个错误.有什么想法吗?

I don't have any indexed views, all foreign keys and triggers are disabled, so I don't know what is causing this error. Any ideas?

推荐答案

将 SET 选项添加到删除调用中.

Add the SET options to the delete call.

这些仍然适用于错误中提到的其他项目，即使您禁用了 FK.

These still apply to the other items mentioned in the error, even though you disabled FKs.

这将解决任何已保存或环境设置

This will work around any saved or environment settings

评论后编辑

EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

相关文章