ALTER INDEX failed because of QUOTED_IDENTIFIER when running from sp_msForEachTable

Question

When I try to rebuild an index on a table:

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

that works fine.

But when I call

EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

I reach the same same table, and it fails with:

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.

---

And to confirm that it's the same table:

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

which gives the results:

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.

What am I not doing wrong?

---

Note:

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

works fine!

Loathly

"What am i not doing wrong?" - there could be a large number of answers to that question :-) – Schear

Answer 1

Quoted identifier settings are stored against each stored procedure, and sp_MSforeachtable has it defined as OFF. However, you can work around this - by setting it to ON before it executes the re-index:

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.

---

And, of course, insert the usual caveats about sp_MSforeachtable being undocumented, and so you can't rely on any of its behaviour being stable.

---

For DBCC DBREINDEX - all bets are off. DBCC lives in its own little, very customized code world. But, of course, it shouldn't be relied on for future work either:

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.

Schear

That's how i discovered the "bug" when using ALTER INDEX; i was using DBCC just fine, and thought i would make my life more miserable by switching to the "preferred" syntax. – Loathly

Answer 2

You need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.

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

Abruption

If the session defaults didn't have the right setting, you might expect that the direct ALTER INDEX call would also fail. – Schear

Answer 3

Do not use sp_msforeachtable. It has been documented to miss object. You would be much better off iterating through a list of tables using 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

For example:

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

  --todo

Averil

It would be cooler if you gave the sql to create an equivalent sp_forEachTable, and infinitely more useful. i would then whore it around to every database i touch. – Loathly

That really is an equivalent. You could turn that into a stored procedure, add the database name & command as parameters, then just call it whenever you need it. – Averil

On the downside: i don't know how to do that. – Loathly

Answer 4

Missing underscore in code example:

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;'

Budget