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

Rate

4.6 (10)

Share

Share

Rate

4.6 (10)