Alter SPs to have all SPs as SET QUOTED_IDENTIFIER ON

In my database set of Stored Procedures has SET QUOTED_IDENTIFIER OFF. I would like to identify them and alter the SPs as SET QUOTED_IDENTIFIER ON. How to perform this with ease. Kindly suggest.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
From SSMS :
1. Go to Object Explorer
2. Expand the Databases collection
3. Expand  your database node.
4. Expand Programmability
5. Select Stored Procedures
6. From the View menu select "Object Explorer Details" or simply press  F7
7. Select all the Stored Procedures you want here in the Object Explorer Details pane.
8. Right click one of the select Stored Procedures in the Object Explorer Details pane and select "Script Stored Procedures as"
9.  Select "Drop and Create To"
10.  Select "New Query Editor Window"
11.  In the new query window Replace all the instances of "SET QUOTED_IDENTIFIER OFF" with "SET QUOTED_IDENTIFIER ON"
12. Execute.
0
 
Ramkisan JagtapLead DeveloperCommented:
Please refer following article http://stackoverflow.com/questions/2147489/change-the-ansi-nulls-setting-for-all-stored-procedures-in-the-database
He has updated ansi null value , likely you can update quoted_identifier value
0
 
Easwaran ParamasivamAuthor Commented:
I saw that already. That is using SMO objects. Is there anyway using TSQL?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
The easiest way is to script them all out are recreate.
0
 
Easwaran ParamasivamAuthor Commented:
@Anthony - Could you please guide how to achieve your suggestion?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can identify only  the ones that have "QUOTED_IDENTIFIER OFF" using this query:

SELECT p.name
FROM sys.procedures p
WHERE
    OBJECTPROPERTYEX(p.object_id, 'ExecIsQuotedIdentOn') = 0

Then you can use "OBJECT_DEFINITION" in a simple cursor loop to ALTER them.  If you need help with that, just let me know.
0
 
Easwaran ParamasivamAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.