/*
* 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 @SearchChar VARCHAR(8000),
@ReplaceChar VARCHAR(8000),
@SearchChar1 VARCHAR(8000),
@Replace BIT
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 @sql varchar(8000),
@ColumnName varchar(100),
@TableName varchar(100)

CREATE TABLE #T (
TableName VARCHAR(100),
FieldName VARCHAR(100),
Value VARCHAR(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
SELECT b.Name as TableName,
c.Name as ColumnName
FROM sysobjects b, syscolumns c
WHERE C.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 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
DROP TABLE #list
close db
deallocate db