Alter every stored procedure in a database that fits a given criteria

Recently I had to search for, and update, hard-coding server references for every stored procedure in a database. This amounted to altering over 60 stored procedures. Instead of going through each stored procedure, one-by-one, I used the following code to accomplish the task:

BEGIN TRANSACTION

CREATE TABLE #ProcsToChange (
	ProcName NVARCHAR(MAX) NULL
)

CREATE TABLE #tblHolding (
	ID INT IDENTITY(1,1) NOT NULL,
	proctext NVARCHAR(MAX) NULL
)

INSERT #ProcsToChange (ProcName)
SELECT DISTINCT OBJECT_NAME(sc.id)
FROM syscomments sc
INNER JOIN sysobjects so 
ON sc.id = so.id
WHERE sc.text LIKE '%[OldServer]%'
ORDER BY OBJECT_NAME(sc.id); 

DECLARE @newProcText NVARCHAR(MAX)
DECLARE @proc NVARCHAR(MAX)

DECLARE c CURSOR FOR
SELECT ProcName FROM #ProcsToChange;

OPEN c;
FETCH NEXT FROM c INTO @proc 

WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT #tblHolding (proctext)
	EXEC sp_helptext @proc
	
	PRINT @proc
	SELECT @newProcText = NULL;
	SELECT @newProcText = COALESCE(@newProcText,'') + proctext FROM #tblHolding ORDER BY ID ASC
	SELECT @newProcText = REPLACE(@newProcText, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
	SELECT @newProcText = REPLACE(@newProcText, '[OldServer]', '[NewServer]');

	--PRINT @newProcText;
	EXEC sp_executesql @newProcText;

	TRUNCATE TABLE #tblHolding
	
	FETCH NEXT FROM c INTO @proc
	
END
CLOSE c;
DEALLOCATE c;

DROP TABLE #ProcsToChange
DROP TABLE #tblHolding

--ROLLBACK
COMMIT

In order to grab only the stored procedures fitting your criteria, simply manipulate the WHERE clause that is used to filter data being inserted into #ProcsToChange.

This code should be thoroughly tested in a test environment before using in production. This code is provided as-is, with no warranty whatsoever.