Turning on Quoted Identifiers for multiple already installed procedures

Question

I have hundreds of procedures that got installed to a database with quoted identifiers set to off and I need them set on. I'm able to view the list of these procedures using the following

SELECT name = OBJECT_NAME([object_id]), uses_quoted_identifier
FROM sys.sql_modules
WHERE uses_quoted_identifier <> 1 AND OBJECT_NAME([object_id]) LIKE '%%'
ORDER BY OBJECT_NAME([object_id])

Now I know I cannot do an update directly to sys.sql_modules to set the uses_quoted_identifier. I could manually open all the scripts and reinstall but that's time consuming. I could also probably make a batch file to run that same process but that's still time consuming albeit slightly less.

Is there an easier way I can go about updating these?

UPDATE While doing some more research I came across this post which made me realize, the quoted identifier stuff is all my own doing because I had a batch command already that was installing procedures from a specific directory: SET QUOTED IDENTIFIER should be ON when inserting a record

I realized using this article I can add -I to my sqlcmd to enable Quoted Identifiers: https://sqlsailor.com/2014/11/14/sqlcmdoptions/

I'll leave this question open for now incase any one has a trick to programmatically update the Quoted Identifiers on procedures but this should fix my issue for now.

Answer 1

I had a similar issue not too long ago where I had to find and fix incorrect quoted identifier settings along with numerous other issues in hundreds of procedures in several databases. Part of the task was to find and remove any explicit set options such as quoted_identifer, ansi_nulls transaction isolation level and many other common issues including removing usage of quotes " in many legacy procedures that used dynamic sql and had quoted_identifer set to off.

This is the bones of my solution which you might like to tinker with to your requirements.

I get the sql text of all the relevant objects - procs, functions etc into a temp table then do various string replaces to fix specific issues. I then iterate through and use dynamic sql to just recreate the objects, which naturally get the correct default set options. Any that fail are indicated in the table and I then deal with manually.

select m.definition, Cast(o.name as varchar(1000)) [name], m.object_id, 0 Done, 0 Success , o.type
--into #working
from sys.sql_modules m join sys.objects o on o.object_id=m.object_id and o.type in ('FN', 'P', 'V', 'TR')
where m.uses_quoted_identifier=0

update #working set definition=Replace(definition, 'create function', 'create or alter function')
update #working set definition=Replace(definition, 'create view', 'create or alter view')
update #working set definition=Replace(definition, 'create trigger', 'create or alter trigger')
update #working set definition=Replace(definition, 'create proc', 'create or alter proc')
update #working set definition=Replace(definition, 'create  proc', 'create or alter proc')
update #working set definition=Replace(definition, 'create proc', 'create or alter proc')
/*
update #working set definition=Replace(definition, 'set ansi_nulls off', '')
update #working set definition=Replace(definition, 'set ansi_warnings off', '')
update #working set definition=Replace(definition, 'set quoted_identifier off', '')
update #working set definition=Replace(definition, '(nolock)', '')
update #working set definition=Replace(definition, 'set transaction isolation level read uncommitted', '')
*/

select * from #working 


declare @Sql nvarchar(max), @Id int

while exists (select * from #working where Done=0)
begin
    select top (1) @Sql=definition, @Id=object_id from #working where Done=0

    update #working set Done=1, Success=1 where object_id=@Id
    begin try
        exec (@Sql)
    end try
    begin catch
        update #working set Success=Error_Number(), name=Error_Message() where object_id=@Id
    end catch
end