What is smart way to search through Store Procedures to replace the old table name with new name?

Consider a scenario: Search through every stored procedure for a string and possibly replace it, like a standard Find/Replace function. 

Many of us sooner or later run into a tedious task of modifying object names in all stored procedures on the server. A column name was changed, a table was renamed, a database on a linked server was moved to another location, etc. 

For example: I have a lot of SPs that are using a table called "Clients".  Now I want to rename my table to Customers, how can I change all the SP without having to open each one manually?

First, you might need to find all the objects where you have used "Clients".

SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON c.Id = o.Id
WHERE --xtype = 'p' AND
 category = 0
  AND c.text LIKE '%Clients%'
ORDER BY o.name

This will list out all the objects where you have used this table.

Second, you might need to script out all the procedures and then use search and replace in text editor to modify them. You might need to write script to automate the tasks. All you need to do is change search and replace strings and it will generate the scripts for you. Here is the complete script:

-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON DECLARE @sqlToRun VARCHAR(1000),
                                 @searchFor VARCHAR(100),
                                            @replaceWith VARCHAR(100) -- text to search for
SET @searchFor = '[MY-SERVER]' -- text to replace with
SET @replaceWith = '[MY-SERVER2]' -- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX)) DECLARE curHelp
CURSOR FAST_FORWARD
FOR -- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string

SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
FROM syscomments
WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
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 stored procedure text into a temporary table
   INSERT INTO @temp
   EXEC (@sqlToRun)
   
   -- add GO after each stored procedure
   INSERT INTO @temp
   VALUES ('GO')
   
   FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE curHelp
DEALLOCATE curHelp

-- find and replace search string in stored procedures 
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'
  CREATE PROCEDURE', '
  ALTER PROCEDURE'),@searchFor,
                    @replaceWith) 
  SELECT spText
  FROM @temp -- now copy and paste result into new window
-- then make sure everything looks good and run
GO