Replace tablename in multiple stored procedures

• smitty-1088185

  SSCrazy

  Points: 2321

  More actions

  March 3, 2015 at 7:21 pm

  #315878

  I'm trying to replace a table name in 250 stored procedures. I found this script below which does

  a good job but it also finds tables with similar names. How can I limit the replacement to the exact

  table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.

  -- 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 = 'MyTable001'

  -- text to replace with

  SET @replaceWith = '[MyTable002]'

  -- 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