Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find and Replace a String in the Whole Database


Find and Replace a String in the Whole Database

Author
Message
Luiz-458831
Luiz-458831
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 646
Comments posted to this topic are about the item Find and Replace a String in the Whole Database
David Leibowitz
David Leibowitz
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 49
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
l.carpay
l.carpay
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 50
just what I needed Luiz. Thanks. Did a small test with the script and it did it's job.
jgoncalves
jgoncalves
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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 @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



l.carpay
l.carpay
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 50
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
starunit
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 2566
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
regemail
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
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!
Luiz-458831
Luiz-458831
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 646
Did you set @Replace to true (SET @Replace = 1)?
regemail
regemail
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Yes, I did. However, I found a simpler script that worked:

http://itknowledgeexchange.techtarget.com/sql-server/tsql-code-to-remove-sql-injection-values-from-your-tables/
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8672 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search