Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find and Replace a String in the Whole Database Expand / Collapse
Author
Message
Posted Saturday, February 9, 2008 2:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 74, Visits: 523
Comments posted to this topic are about the item Find and Replace a String in the Whole Database
Post #453569
Posted Friday, April 11, 2008 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 6, 2009 7:23 AM
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
Post #483642
Posted Wednesday, October 26, 2011 11:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 22, 2013 6:27 AM
Points: 13, Visits: 50
just what I needed Luiz. Thanks. Did a small test with the script and it did it's job.
Post #1196739
Posted Thursday, October 27, 2011 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 11:13 AM
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


Post #1196791
Posted Thursday, October 27, 2011 3:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 22, 2013 6:27 AM
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
Post #1196794
Posted Wednesday, November 2, 2011 11:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:44 AM
Points: 121, Visits: 2,443
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.
Post #1199475
Posted Sunday, January 26, 2014 1:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 3:13 PM
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!
Post #1534772
Posted Monday, January 27, 2014 7:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 74, Visits: 523
Did you set @Replace to true (SET @Replace = 1)?
Post #1535014
Posted Monday, January 27, 2014 3:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 3:13 PM
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/
Post #1535227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse