Find and Replace a String in the Whole Database

Question

Find and Replace a String in the Whole Database

Luiz-458831

SSCommitted

Points: 1904
More actions
February 9, 2008 at 2:30 am

#203172

Comments posted to this topic are about the item Find and Replace a String in the Whole Database

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Leibowitz SSC Veteran Points: 244 More actions · Answer 1

A couple of issues with the script..

Lots of hidden ascii spaces, so you can't copy/paste into the query window without doing some find replace...this can be a little easier in notepad...but try to strip out the hidden spaces from the post next time.

Also, this script only works on objects owned by the dbo schema. It will fail on the sample AdventureWorks DB in SQL 2005 which makes heavy use of schemas on tables.

Following is the script to populate the db cursor that will work for any owned object (tested in SQL 2005)

declare db cursor for

SELECT '[' + s.NAME + '].[' + b.Name + ']' as TableName,

c.Name as ColumnName

FROM sys.objects b, syscolumns c, sys.schemas s

WHERE C.id = b.OBJECT_ID --b.id

and b.type='u'

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

AND s.SCHEMA_ID = b.schema_id

order BY b.name

My blog: jetlounge.net

2903r87fggh SSC Veteran Points: 213 More actions · Answer 2

just what I needed Luiz. Thanks. Did a small test with the script and it did it's job.

jgoncalves SSC Rookie Points: 25 More actions · Answer 3

I was looking for something like this for a while.

I found necessary to add exceptions, as i did not want to look in all tables. so i added this feature, passed as a string with parameters separated by commas.

/*
* 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

2903r87fggh SSC Veteran Points: 213 More actions · Answer 4

great Joao, I was planning to do the same thing and put it in an USP. Thanks for the next iteration. I plan to do my part by the end of next month. I have to go on a vacation first 😉

starunit SSCommitted Points: 1817 More actions · Answer 5

wow. i wish I had this 11 years ago when I was tasked with a Y to K project - had to change all Y's to K's just before midnight, 1999-12-31. (side note: I lost that job the following day... )

Cheers,

Mark
Just a cog in the wheel.

regemail SSC Enthusiast Points: 144 More actions · Answer 6

Hi- I have 1000+ records with the text 'fly' in multiple different tables and multiple different columns across one SQL Server 2005 database. I ran this script to replace 'fly' with 'read', but no records were modified. Any thoughts? Thanks so much in advance for all helpful responses!