search and replace a string in all user tables’ data in all databases

http://www.sqlservercentral.com/scripts/T-SQL/76518/

1) Create the stored procedure in one of the databases.

2) Run the procedure by passing the required parameters(@FindString - string to be searched,@ReplaceString - string to be replaced by)

3)The step 2 will generate the script of update statements.

4)Verify the script and then execute the same.

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReplaceString]    Script Date: 09/05/2014 15:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* 
Exec [usp_ReplaceString] 'abc','xyz' 
*/ 
/*****************************************************/ 

ALTER PROCEDURE [dbo].[usp_ReplaceString]

( 
@FindString varchar(100),
@ReplaceString varchar(100) 
) 
AS 

BEGIN 

SET NOCOUNT ON 

CREATE TABLE #FilteredTables(DatabaseID int,DatabaseName varchar(255),TableName varchar(255),ColumnName varchar(128)) 
CREATE TABLE #AllDbTables(DatabaseID int,DatabaseName varchar(255),TableName varchar(255),TableId int) 
CREATE TABLE #AllDbTableColumns(DatabaseID int,DatabaseName 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
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(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,TableName,TableID FROM #AllDbTables
OPEN cur_Columns 
FETCH NEXT FROM cur_Columns INTO @DatabaseId,@DatabaseName,@TableName,@TableID
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
set @cmd = 'insert into #AllDbTableColumns select '''+cast(@DatabaseId as varchar)+''','''+@DatabaseName+''','''+@TableName+''',QUOTENAME(name) FROM '+@DatabaseName+'.sys.columns WHERE object_id = '+cast(@TableID as varchar)+' AND system_type_id IN (167, 175, 231, 239, 99, 35)'
exec(@cmd)
FETCH NEXT FROM cur_Columns INTO @DatabaseId,@DatabaseName,@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,TableName,ColumnName FROM #AllDbTableColumns
OPEN cur_Filtered 
FETCH NEXT FROM cur_Filtered INTO @DatabaseId,@DatabaseName,@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+'.dbo.'+@TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindString + '%'') insert into #FilteredTables select '''+cast(@DatabaseId as varchar)+''','''+@DatabaseName+''',''' + @TableName + ''', ''' + @ColumnName + '''' 

EXEC(@cmd) 

FETCH NEXT FROM cur_Filtered INTO @DatabaseId,@DatabaseName,@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,TableName,ColumnName FROM #FilteredTables 
OPEN cur_Replace 
FETCH NEXT FROM cur_Replace INTO @DatabaseId,@DatabaseName,@TableName,@ColumnName 
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
SET @cmd = ' UPDATE ' + @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,@TableName,@ColumnName 

END 
CLOSE cur_Replace 
DEALLOCATE cur_Replace

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


END