Thanks for pointing out this..:-)

I have made some changes in the script to handle this. Please check out the modified script..

ALTER PROCEDURE [dbo].[usp_ReplaceString]

(

@FindString varchar(100),

@ReplaceString varchar(100)

)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #FilteredTables(DatabaseID int,DatabaseName varchar(255),SchemaName varchar(255), TableName varchar(255),ColumnName varchar(128))

CREATE TABLE #AllDbTables(DatabaseID int,DatabaseName varchar(255),SchemaName varchar(255),TableName varchar(255),TableId int)

CREATE TABLE #AllDbTableColumns(DatabaseID int,DatabaseName varchar(255),SchemaName varchar(255),TableName varchar(255),ColumnName varchar(128))

DECLARE @DatabaseName nvarchar(255),@DatabaseID int,@TableName nvarchar(255), @ColumnName nvarchar(128),@cmd nvarchar(4000),@ErrorMsg varchar(4000),

@WhrFindString varchar(100),@TableId int,@Id int, @SchemaName varchar(255)

SET @WhrFindString = QUOTENAME('%' + @FindString + '%','''')

SET @cmd = ''

SET @Id = 0

-- Get tables from all the databases

DECLARE cur_Databases CURSOR FOR

SELECT QUOTENAME(name),database_id FROM sys.databases where name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

OPEN cur_Databases

FETCH NEXT FROM cur_Databases INTO @DatabaseName,@DatabaseID

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @cmd = 'insert into #AllDbTables select '''+cast(@DatabaseID as varchar)+''','''+@DatabaseName+''', QUOTENAME(SCHEMA_NAME(schema_id)), QUOTENAME(name)as TableName, object_id as TableID from '+@DatabaseName+'.sys.objects where type = ''u'' and is_ms_shipped = 0'

EXEC(@cmd)

FETCH NEXT FROM cur_Databases into @DatabaseName,@DatabaseID

END

CLOSE cur_Databases

DEALLOCATE cur_Databases

--Get columns for tables from all databases

DECLARE cur_Columns CURSOR FOR

SELECT DatabaseId,DatabaseName,SchemaName, TableName,TableID FROM #AllDbTables

OPEN cur_Columns

FETCH NEXT FROM cur_Columns INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@TableID

WHILE (@@FETCH_STATUS = 0)

BEGIN

set @cmd = 'insert into #AllDbTableColumns select '''+cast(@DatabaseId as varchar)+''','''+@DatabaseName+''','''+@SchemaName+''','''+@TableName+''',QUOTENAME(name) FROM '+@DatabaseName+'.sys.columns WHERE object_id = '+cast(@TableID as varchar)+' AND system_type_id IN (167, 175, 231, 239, 35)'

exec(@cmd)

FETCH NEXT FROM cur_Columns INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@TableID

end

CLOSE cur_Columns

DEALLOCATE cur_Columns

--Get table columns for only the String match

DECLARE cur_Filtered CURSOR FOR

SELECT DatabaseId,DatabaseName,SchemaName, TableName,ColumnName FROM #AllDbTableColumns

OPEN cur_Filtered

FETCH NEXT FROM cur_Filtered INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@ColumnName

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Get only those tables and the particular column for which the match has found and inserts these details in temp table.

SET @cmd = 'IF EXISTS (SELECT * FROM '+ @DatabaseName+'.'+@SchemaName+'.'+@TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindString + '%'') insert into #FilteredTables select '''+cast(@DatabaseId as varchar)+''','''+@DatabaseName+''',''' + @SchemaName+''','''+@TableName + ''', ''' + @ColumnName + ''''

EXEC(@cmd)

FETCH NEXT FROM cur_Filtered INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@ColumnName

END

CLOSE cur_Filtered

DEALLOCATE cur_Filtered

SET @cmd = ''

IF EXISTS(SELECT * FROM #FilteredTables)

BEGIN

--- Cursor for creating update statement for the tables filtered above.

DECLARE cur_Replace CURSOR FOR

SELECT DatabaseId,DatabaseName,SchemaName, TableName,ColumnName FROM #FilteredTables

OPEN cur_Replace

FETCH NEXT FROM cur_Replace INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@ColumnName

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @cmd = ' UPDATE ' + @SchemaName+'.'+@TableName +

' SET ' + @ColumnName

+ ' = REPLACE(' + @ColumnName + ', '

+ QUOTENAME(@FindString, '''') + ', ' + QUOTENAME(@ReplaceString, '''') +

') WHERE ' + @ColumnName + ' LIKE ' + @WhrFindString +CHAR(13)+';'

if(@Id != @DatabaseId)

begin

SET @cmd = 'USE '+@DatabaseName+';'+char(13)+@cmd+char(13)

end

PRINT @cmd

SET @Id = @DatabaseId

FETCH NEXT FROM cur_Replace INTO @DatabaseId,@DatabaseName,@SchemaName, @TableName,@ColumnName

END

CLOSE cur_Replace

DEALLOCATE cur_Replace

END

ELSE

BEGIN

PRINT 'No matche(s) Found for string '''+@FindString +''''

END

END