Binaryworld - search and replace substring ... [ SQL Server -> Stored Procedure ]

/*
This stored procedure can be used to search and replace substring
in the char, nchar, varchar and nvarchar columns in all tables
in the current database. You should pass the text value to
search and the text value to replace. So, to replace all
char, nchar, varchar and nvarchar columns which contain
the substring 'John' with the substring 'Bill', you can use the
following (in comparison with the SetTbColValues stored procedure,
this stored procedure replace only substring, not the entire
column's value):

EXEC replace_substring @search_value = 'John',
            @replace_value = 'Bill'
*/

IF OBJECT_ID('replace_substring') IS NOT NULL DROP PROC replace_substring
GO

CREATE PROCEDURE replace_substring (
@search_value varchar(128) = null,
@replace_value varchar(128) = null
)
AS
DECLARE
@execstr varchar(1000),
@objectname sysname,
@colname sysname

SET NOCOUNT ON
IF @search_value IS NULL
BEGIN
  RAISERROR ('You must specify the value to search', 16, 1)
  RETURN
END
IF @replace_value IS NULL
BEGIN
  RAISERROR ('You must specify the value to replace', 16, 1)
  RETURN
END

DECLARE tb_fetch_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tb_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
WHILE (@@fetch_status <> -1)
BEGIN
  DECLARE col_fetch_cursor CURSOR FOR
   SELECT name FROM syscolumns WHERE id = OBJECT_ID(@objectname) AND type IN
    (SELECT type FROM systypes WHERE name = 'char' OR name = 'nchar' OR name = 'varchar' OR name = 'nvarchar')
  OPEN col_fetch_cursor
  FETCH NEXT FROM col_fetch_cursor INTO @colname
  WHILE (@@fetch_status <> -1)
   BEGIN
    SELECT @execstr = 'IF EXISTS (SELECT * FROM ' + @objectname + ' WHERE ' + @colname + ' like ''%' + @search_value + '%'') BEGIN SELECT ''' + @objectname + ''' as tbname, ''' + @colname + ''' as colname PRINT '''' UPDATE ' + @objectname + ' SET ' + @colname + ' = REPLACE(' + @colname + ',''' + @search_value + ''',''' + @replace_value + ''') WHERE ' + @colname + ' like ''%' + @search_value + '%'' END'
    EXEC (@execstr)
    FETCH NEXT FROM col_fetch_cursor INTO @colname
   END
  DEALLOCATE col_fetch_cursor
  FETCH NEXT FROM tb_fetch_cursor INTO @objectname
END
DEALLOCATE tb_fetch_cursor
GO