By Callum 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.
Troubleshoot and fix sp_msforeachtable QUOTED_IDENTIFIER error
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;’
ree
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 ?”’