Find and Replace a String in the Whole Database
To just look for a string occurences in the whole database, set @Replace to 0. If you want to find and replace, set @Replace to 1.
/*
* CATEGORY: Script
* AUTHOR: Luiz Barros
* OBJECTIVE: Find and Replace a string in all string fields (char, varchar, etc) of all tables in the database
*
* PARAMETERS:
* @SearchChar is the string to be found. Use wildcard %
* @ReplaceChar is the string to replace occurrences of @SearchChar
* @Replace=0 => search for @SearchChar; @Replace=1 => Find and replace occurrences
*/
SET NOCOUNT ON
DECLARE @SearchChar VARCHAR(8000),
@ReplaceChar VARCHAR(8000),
@SearchChar1 VARCHAR(8000),
@Replace BIT
SET @Replace = 0 -- 0 => only find; 1 => replace
SET @SearchChar = '%FIND THIS STRING%' -- Like 'A%', '%A' or '%A%'
SET @ReplaceChar = 'REPLACE BY THIS STRING' -- don't use wildcards here
IF @Replace=1 AND (@SearchChar IS NULL OR @ReplaceChar IS NULL) BEGIN
PRINT 'Invalid Parameters' Return
END
SET @SearchChar1 = REPLACE(@SearchChar, '%', '')
declare @sql varchar(8000),
@ColumnName varchar(100),
@TableName varchar(100)
CREATE TABLE #T (
TableName VARCHAR(100),
FieldName VARCHAR(100),
Value VARCHAR(Max)
)
declare db cursor for
SELECT b.Name as TableName,
c.Name as ColumnName
FROM sysobjects b, syscolumns c
WHERE C.id = b.id
and b.type='u'
AND c.xType IN (35, 99, 167, 175, 231, 239) -- string types
order by b.name
open db
fetch next from db into @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0 BEGIN
IF @Replace = 0
SET @sql = 'INSERT #T SELECT '''+@TableName+''', ''' +@ColumnName+ ''', ['+@ColumnName+'] FROM '+@TableName+' WHERE ['+@ColumnName+'] LIKE '''+@SearchChar+''''
ELSE
SET @sql = 'UPDATE '+@TableName+' SET ['+@ColumnName+'] = REPLACE(convert(varchar(max),['+@ColumnName+']),'''+@SearchChar1+''','''+@ReplaceChar+''') WHERE ['+@ColumnName+'] LIKE '''+@SearchChar+''''
EXEC(@sql)
print @TableName+' - '+@ColumnName
fetch next from db into @TableName, @ColumnName
END
IF @Replace=0 SELECT * FROM #T ORDER BY TableName
DROP TABLE #T
close db
deallocate db