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
When a stored procedure is created,the
SET QUOTED_IDENTIFIERandSET ANSI_NULLSsettings 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.
