Troubleshoot and fix sp_msforeachtable QUOTED_IDENTIFIER error

Background

When utilizing the sp_msforeachtable procedure to rebuild an index on all tables within a database, the undocumented stored procedures iterate through each table individually. This can be achieved using the following T-SQL command:

EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

While running the SQL command, I received the error "Msg 1934, Level 16, State 1, Line 1

ALTER INDEX failed due to incorrect settings of 'QUOTED_IDENTIFIER'."

Error Message:

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.

Solution

You need to set QUOTED_IDENTIFIER ON in the sp_msForEachTable because it does not have the right setting.

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

You can further troubleshoot to find the problematic object using the below T-SQL

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