Script to replace ocurrences in all Functions, Views and Stored Procedures - SQL SERVER

String_Replace_SysObjects.sql

-- Include strings to replace in @ServerList


	DECLARE @ServerList	TABLE (Id INT IDENTITY(1,1), Prod VARCHAR(MAX), Dev VARCHAR(MAX))

	INSERT INTO @ServerList (Prod,Dev) VALUES
	('String_In_Prod001', 'String_In_Dev001')
	,('String_In_Prod002', 'String_In_Prod002')



	/*########################## Replacing! ################################*/
	DECLARE @ObjectList		TABLE (Id INT IDENTITY(1,1), ObjectId INT, ObjectName VARCHAR(MAX), ObjectType VARCHAR(MAX) ,ObjectText NVARCHAR(MAX))
	DECLARE @ErrorList		TABLE (Id INT IDENTITY(1,1), ObjectText NVARCHAR(MAX), ErrorText VARCHAR(MAX))
	DECLARE @TempQuery		NVARCHAR(MAX)
	DECLARE @QtdObj			INT
	DECLARE @QtdServerList	INT
	DECLARE @LoopObj		INT = 1
	DECLARE @LoopSrv		INT
	DECLARE @Dummy			INT

	SELECT @QtdServerList = COUNT(*) FROM @ServerList

	--Adding SCAPES to brackets

	UPDATE @ServerList SET Prod = REPLACE(Prod,'\','\\'), Dev = REPLACE(Dev,'\','\\')
	UPDATE @ServerList SET Prod = REPLACE(Prod,'[','\['), Dev = REPLACE(Dev,'[','\[')
	UPDATE @ServerList SET Prod = REPLACE(Prod,']','\]'), Dev = REPLACE(Dev,']','\]')


	SET @TempQuery = 
		'SELECT 
			T01.Object_Id
			,T02.Name
			,(CASE 
				WHEN T02.Type = ''TF'' THEN ''FUNCTION''
				WHEN T02.Type = ''P'' THEN ''PROCEDURE''
				WHEN T02.Type = ''FN'' THEN ''FUNCTION''
				WHEN T02.Type = ''V'' THEN ''VIEW''
			END)
			,T01.Definition
		FROM sys.sql_modules T01 JOIN sys.objects T02 ON  T01.object_id = T02.object_id 
		WHERE '


	SET @TempQuery += (Select ' OR definition LIKE ''%' + Prod + '%'' ESCAPE ''\'''  AS [text()]
	From @ServerList
	For XML PATH (''))

	--Removing first "OR"
	SET @TempQuery = REPLACE(@TempQuery,'WHERE  OR','WHERE')

	INSERT INTO @ObjectList
	EXEC SP_ExecuteSql @TempQuery

	--Removing SCAPES from brackets
	UPDATE @ServerList SET Prod = REPLACE(Prod,'\\','\'), Dev = REPLACE(Dev,'\\','\')
	UPDATE @ServerList SET Prod = REPLACE(Prod,'\[','['), Dev = REPLACE(Dev,'\[', '[')
	UPDATE @ServerList SET Prod = REPLACE(Prod,'\]',']'), Dev = REPLACE(Dev,'\]', ']')

	SELECT @QtdObj = COUNT(*) FROM @ObjectList
	--SELECT * FROM @ObjectList
	WHILE (@LoopObj <= @QtdObj) BEGIN
		SET @LoopSrv = 1
		SELECT @TempQuery = REPLACE(ObjectText,'CREATE '+ObjectType, 'ALTER '+ObjectType) FROM @ObjectList WHERE Id = @LoopObj

		WHILE (@LoopSrv <= @QtdServerList) BEGIN
			SELECT @TempQuery = REPLACE(@TempQuery,Prod,Dev) FROM @ServerList WHERE Id = @LoopSrv
			SET @LoopSrv += 1
		END
		BEGIN TRY
			EXEC SP_ExecuteSql @TempQuery
			SELECT @Dummy = 1
		END TRY
		BEGIN CATCH
			INSERT INTO @ErrorList VALUES (@TempQuery, ERROR_MESSAGE())
		END CATCH
		SET @LoopObj += 1
	END


SELECT * FROM @ErrorList