Script to replace a particular string in all the SP.


SET NOCOUNT ON

DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)


SET @searchFor = '[MY-SERVER]'

SET @replaceWith = '[MY-SERVER2]'

DECLARE @temp TABLE (spText VARCHAR(MAX))

DECLARE curHelp CURSOR
FOR
SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
FROM syscomments
--enter objectid
--where
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

OPEN curHelp

FETCH next FROM curHelp INTO @sqlToRun

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO @temp
   EXEC (@sqlToRun)

 
   INSERT INTO @temp
   VALUES ('GO')

   FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE curHelp
DEALLOCATE curHelp


UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)

SELECT spText FROM @temp

GO