/*

* 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

* @exceptlist is the list of exceptions, string separated by commas. ex: 'excepTable1,excepTable2'

*/

SET NOCOUNT ON

DECLARE@SearchCharVARCHAR(8000),

@ReplaceCharVARCHAR(8000),

@SearchChar1VARCHAR(8000),

@ReplaceBIT

DECLARE @pos int

DECLARE @exceptlist VARCHAR(4000)

SET @Replace = 0 -- 0 => only find; 1 => replace

SET @SearchChar = '%actividade%' -- Like 'A%', '%A' or '%A%'

SET @ReplaceChar = 'REPLACE BY THIS STRING' -- don't use wildcards here

SET @exceptlist = 'excepTable1,excepTable2' -- list of exceptions

IF @Replace=1 AND (@SearchChar IS NULL OR @ReplaceChar IS NULL) BEGIN

PRINT 'Invalid Parameters' Return

END

SET @SearchChar1 = REPLACE(@SearchChar, '%', '')

declare@sqlvarchar(8000),

@ColumnNamevarchar(100),

@TableNamevarchar(100)

CREATE TABLE #T (

TableNameVARCHAR(100),

FieldNameVARCHAR(100),

ValueVARCHAR(Max)

)

--create table to hold parsed values

CREATE TABLE #list (val varchar(10))

--add comma to end of list

SET @exceptlist = @exceptlist + ','

--loop through list

WHILE CHARINDEX(',', @exceptlist) > 0

BEGIN

--get next comma position

SET @pos = CHARINDEX(',', @exceptlist)

--insert next value into table

INSERT #list VALUES (LTRIM(RTRIM(LEFT(@exceptlist, @pos - 1))))

--delete inserted value from list

SET @exceptlist = STUFF(@exceptlist, 1, @pos, '')

END

declare db cursor for

SELECTb.Name as TableName,

c.Name as ColumnName

FROMsysobjects b, syscolumns c

WHEREC.id = b.id AND b.name not in (SELECT val FROM #list)

and b.type='u'

AND c.xType IN (35, 99, 167, 175, 231, 239) -- string types

order byb.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

DROP TABLE #list

close db

deallocate db