How to find of the QUOTED_IDENTIFIER used for stored procedure

Question: When executing a stored procedure I received an 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.

I’ve checked my SSMS settings which indicated SET QUOTED IFDENTIFIER ON, so I’m slightly confused about why this is not applying to the stored procedure I’m executing. Is there a method to present the QUOTED_IDENTIFIER value used when the stored procedure was created ?

Answer: It's worth understanding there is a difference between executing a batch statement through SSMS and executing a stored procedure .

Executing through the SSMS will pick up the value set at the SSMS level. Unless you override the setting by using SET QUOTED_IDENTIFIER OFF | ON at the top of your sql batch statement

These settings won’t apply if you are executing a stored procedure , for example : exec mySP1 . It uses the QUOTED_IDENTIFIER setting used when the stored procedure was created . How can you find out the QUOTED_IDENTIFIER value used when the stored procedure was created. ?

This query using sys.sql_modules view the will return the QUOTED_IDENTIFIER value used when the stored was created.

SELECT name = OBJECT_NAME([object_id]), ,uses_quoted_identifier
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N',mysp1', N'mysp2');

If you want to change the QUOTED_IDENTIFIER value , you can recreate the stored procedure but this time apply SET QUOTED_IDENTIFIER ON

Read More  

SQL Server – ALTER INDEX failed due to QUOTED IDENTIFIER set ...

SQL Server - Last time a stored procedure was executed