Ошибка ALTER INDEX из-за QUOTED_IDENTIFIER при запуске из sp_msForEachTable

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

работает отлично!

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 вместо.

Answer 2

Вам нужно SET QUOTED_IDENTIFIER ON в sp_msForEachTable а потому что sp_msForEachTable не имеет правильной настройки.

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

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

Answer 4

В примере кода отсутствует подчеркивание:

БЫЛ

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

ЯВЛЯЕТСЯ

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