从sp_msForEachTable运行时，由于QUOTED_IDENTIFIER的原因ALTER INDEX失败

Question

当我尝试重建一个表上的索引时：

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

那个很好用。

但是当我调用时

EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

我遇到了同样的表格，并且出现了以下错误：

消息1934，级别16，状态1，行2
ALTER INDEX失败，因为以下SET选项设置不正确：'QUOTED_IDENTIFIER'。请验证SET选项是否正确用于索引视图和/或计算列上的索引和/或过滤索引和/或查询通知和/或XML数据类型方法和/或空间索引操作。

---

并且确认这是相同的表格：

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

运行良好!

- Ian Boyd

---

“我到底做错了什么？” - 对于这个问题可能会有很多答案 :-) - Damien_The_Unbeliever

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：

存储过程创建时，会捕捉 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置，并用于后续对该存储过程的调用。

---

当然，需要注意的是 sp_MSforeachtable 是未记录的，因此不能保证其行为是稳定的。

---

对于 DBCC DBREINDEX - 一切都不确定。 DBCC 存在于自己的小世界中，具有非常定制化的代码。但是，当然也不应依赖它进行未来工作：

此功能将在 Microsoft SQL Server 的将来版本中删除。 不要在新开发工作中使用此功能，并尽快修改当前使用此功能的应用程序。使用 ALTER INDEX 替代。

- Damien_The_Unbeliever

---

这就是我在使用ALTER INDEX时发现“bug”的方式；我一直很好地使用DBCC，并认为通过切换到“首选”语法会让我的生活更加痛苦。 - Ian Boyd

Answer 2

您需要在sp_msForEachTable中也使用SET QUOTED_IDENTIFIER ON，因为sp_msForEachTable没有正确的设置。

EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;'

- RichardTheKiwi

---

如果会话默认设置不正确，你可能会预期直接调用 ALTER INDEX 也会失败。 - Damien_The_Unbeliever

Answer 3

不要使用sp_msforeachtable。它已经被记录为会漏掉对象。你最好使用sys.tables迭代一个表列表。

DECLARE @id INT ,
    @table NVARCHAR(256) ,
    @reindex NVARCHAR(4000)
SELECT  @id = MIN(object_id)
FROM    sys.tables
WHILE @id IS NOT NULL 
    BEGIN
        SELECT  @table = QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
        FROM    sys.tables t
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE   t.object_id = @id

        SELECT  @reindex = 'SET QUOTED IDENTIFIER ON; ALTER INDEX ALL ON '
                + @table + ' REBUILD;'
        PRINT @reindex --prints the reindex command
        --EXEC @reindex --uncomment to actually reindex

        SELECT  @id = MIN(object_id)
        FROM    sys.tables
        WHERE   object_id > @id
   END

例如：

CREATE PROCEDURE dbo.sp_ForEachTable @query varchar(8000) AS

  --todo

- Nic

---

如果您提供了创建等效的“sp_forEachTable”的SQL语句，那将会更加酷，并且无限地提高其实用性。然后我会在我接触到的每个数据库中使用它。 - Ian Boyd

那确实是一个等价物。你可以将其转换为存储过程，添加数据库名称和命令作为参数，然后在需要时调用它。 - Nic

缺点是：我不知道如何做到那一点。 - Ian Boyd

Answer 4

代码示例中缺少下划线：

WAS

ELECT  @reindex = 'SET QUOTED IDENTIFIER ON; ALTER INDEX ALL ON '
                + @table + ' REBUILD;'

IS

ELECT  @reindex = 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON '
                + @table + ' REBUILD;'

- Robert Smith