top of page

Troubleshoot and fix sp_msforeachtable QUOTED_IDENTIFIER error

Updated: 7 days ago

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.



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



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

27 views1 comment

Recent Posts

See All

1 Comment


han gu
han gu
5 days ago

如果您还在怀疑是否应该订购Essay代写 https://www.essayghostwriting.com/ 服务,我们可以向您保证从头开始写的原创内容和及时交付您的Essay。您从我们这里收到的Essay将是100%独特的 – 不是从互联网上复制粘贴的。由于我们以客户为中心的原则,每个客户需求都能被满足。在帮助客户撰写Essay的同时,我们的专家也会将撰写技巧教给同学们,真正让大家掌握撰写Essay的精髓,提升同学们的综合学术水平。

Like
bottom of page