sql-server – 从sp_msForEachTable运行时,由于QUOTED_IDENTIFIE

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,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(''?'')'

工作良好！

解决方法

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生活在自己的小型,非常定制的代码世界中.但是,当然,它不应该依赖于未来的工作：

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 07002 instead.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!