SET NOCOUNT ON

DECLARE @name sysname = N'',
	@proc_definition NVARCHAR(MAX) = N'',
	@proc_permissions NVARCHAR(MAX) = N'',
	@crlf NVARCHAR(4) = CHAR(13) + CHAR(10),
	@print_only BIT = 1


DECLARE @procs_tbl TABLE (
	[object_id] INT NOT NULL,
	[name] NVARCHAR(256) NOT NULL,
	[proc_definition] NVARCHAR(MAX) NOT NULL
)

-- find all the procs with bad options, and jam them into the table variable
-- add the options change, and the if exists drop to the beginning of the script
INSERT INTO @procs_tbl (
    object_id,
    name,
    proc_definition
)
SELECT sm.object_id,
	fn.name,
	N'SET QUOTED_IDENTIFIER, ANSI_NULLS ON;' + @crlf + N'GO' + @crlf +
	N'IF OBJECT_ID(''' + fn.name + ''') IS NOT NULL BEGIN DROP PROCEDURE ' + fn.name + N' END'  + @crlf + N'GO' + @crlf +
	sm.definition
FROM sys.sql_modules sm
CROSS APPLY (
	SELECT [name] = QUOTENAME(OBJECT_SCHEMA_NAME(sm.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sm.object_id))
) fn
WHERE (
	sm.uses_ansi_nulls = 0 OR sm.uses_quoted_identifier = 0
) AND OBJECTPROPERTY(sm.object_id, 'IsMSShipped') = 0
AND OBJECTPROPERTY(sm.object_id, 'IsProcedure') = 1

SELECT * FROM @procs_tbl

-- generate all the grants off all the procs we are about to recreate
SELECT @proc_permissions += p.state_desc + ' ' + p.permission_name + N' ON '
	+ prc.name + N' TO ' + USER_NAME(p.grantee_principal_id) + ';' + @crlf COLLATE Latin1_General_CI_AS
FROM sys.database_permissions p
INNER JOIN @procs_tbl prc ON p.major_id = prc.object_id

DECLARE bad_options_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name, proc_definition
FROM @procs_tbl

OPEN bad_options_cursor
FETCH NEXT FROM bad_options_cursor INTO @name, @proc_definition

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT '/********** Altering: ' + @name + ' **********/'
	PRINT @proc_definition
	IF @print_only = 0 BEGIN
		EXEC (@proc_definition)
	END
    FETCH NEXT FROM bad_options_cursor
	INTO @name, @proc_definition
END

CLOSE bad_options_cursor
DEALLOCATE bad_options_cursor

PRINT ''
PRINT 'GO'
PRINT @proc_permissions
IF @print_only = 0 BEGIN
	EXEC (@proc_permissions)
END