Change Text in Stored Procedure on Microsoft SQL Server

I did some work for a client recently creating a script which they could run after copying their live Microsoft Dynamics GP system to a development server. This script did a variety of tasks, one of which was to change the folder location to which Post Master Enterprise was exporting a generated SSRS report as a PDF; this path was embedded within a stored procedure.

I’ve previously written a SQL script which updated SQL views. I was able to use that script as the basis for this one which replaces text within a specific stored procedure.

The first highlighted section is the path on the live server, the second the path on the dev server and the third is the name of the stored procedure to be updated:

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SprocDefinition AS NVARCHAR(max)
SELECT @SprocDefinition =
	REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE PROC', 'ALTER PROC')
		,'\\SQL01\Dynamics Central\CompanyA\Invoices\','\\SQL02\Dynamics Central\CompanyA\Invoices\')
FROM
	sys.all_objects AS ['All Objects']
LEFT JOIN
	sys.sql_modules AS ['SQL Modules']
		ON
			['SQL Modules'].object_id = ['All Objects'].object_id
WHERE
	['All Objects'].name = 'zDP_ESS80000_PostSSRS'
EXEC (@SprocDefinition)
GO

Read original post Change Text in Stored Procedure on Microsoft SQL Server at azurecurve|Ramblings of an IT Professional