sql-server - 从 sp_msForEachTable 运行时，由于 QUOTED_IDENTIFIER，ALTER INDEX 失败

Question

当我尝试在表上重建索引时:

ALTER INDEX ALL ON [dbo].[Allocations] REBUILD

效果很好。

但是当我打电话时

EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

我到达同一张表，但失败了:

Msg 1934, Level 16, State 1, Line 2
ALTER INDEX 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.

<小时/>

并确认它是同一个表:

EXECUTE sp_msForEachTable 'print ''Rebuilding ?'';
ALTER INDEX ALL ON ? REBUILD;
PRINT ''   Done ?'''

给出结果:

Rebuilding [dbo].[SystemConfiguration]
   Done [dbo].[SystemConfiguration]
Rebuilding [dbo].[UserGroups]
   Done [dbo].[UserGroups]
Rebuilding [dbo].[Groups]
   Done [dbo].[Groups]
Rebuilding [dbo].[UserPermissions]
   Done [dbo].[UserPermissions]
Rebuilding [dbo].[AllocationAdmins]
   Done [dbo].[AllocationAdmins]
Rebuilding [dbo].[Allocations]
Msg 1934, Level 16, State 1, Line 2
ALTER INDEX 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.

我没有做错什么？

<小时/>

注意:

EXECUTE sp_msForEachTable 'DBCC DBREINDEX(''?'')' 

工作很好!

Answer 1

引用的标识符设置针对每个存储过程进行存储，并且 sp_MSforeachtable 将其定义为 OFF。但是，您可以解决此问题 - 在执行重新索引之前将其设置为 ON:

create table dbo.T (
    ID int not null,
    constraint PK_T PRIMARY KEY (ID)
)
go
create view dbo.V ( ID)
with schemabinding
as
    select ID from dbo.T
go
create unique clustered index IX_V on dbo.V(ID)
go
ALTER INDEX ALL ON dbo.V REBUILD --Fine
go
exec sp_MSforeachtable  'ALTER INDEX ALL ON ? REBUILD' --Errors
go
exec sp_MSforeachtable  'SET QUOTED_IDENTIFIER ON;
ALTER INDEX ALL ON ? REBUILD' --Fine

<小时/>

SET QUOTED_IDENTIFIER :

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

<小时/>

当然，请插入有关 sp_MSforeachtable 未记录的常见警告，因此您不能依赖其任何稳定的行为。

<小时/>

对于DBCC DBREINDEX - 所有的赌注都失败了。 DBCC 生活在它自己的小而定制化的代码世界中。但是，当然， future 的工作也不应该依赖它:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.