SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Luiz Barros / Find and Replace a String in the Whole Database / Latest Posts

RE: Find and Replace a String in the Whole Database

starunit — Wed, 02 Nov 2011 11:12:49 GMT

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,

RE: Find and Replace a String in the Whole Database

l.carpay — Thu, 27 Oct 2011 03:40:33 GMT

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 ;-)

RE: Find and Replace a String in the Whole Database

jgoncalves — Thu, 27 Oct 2011 03:35:45 GMT

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.[quote]/** 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 ONDECLARE @SearchChar VARCHAR(8000), @ReplaceChar VARCHAR(8000), @SearchChar1 VARCHAR(8000), @Replace BITDECLARE @pos intDECLARE @exceptlist VARCHAR(4000)SET @Replace = 0 -- 0 => only find; 1 => replaceSET @SearchChar = '%actividade%' -- Like 'A%', '%A' or '%A%'SET @ReplaceChar = 'REPLACE BY THIS STRING' -- don't use wildcards hereSET @exceptlist = 'excepTable1,excepTable2' -- list of exceptionsIF @Replace=1 AND (@SearchChar IS NULL OR @ReplaceChar IS NULL) BEGIN PRINT 'Invalid Parameters' ReturnENDSET @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 valuesCREATE TABLE #list (val varchar(10))--add comma to end of listSET @exceptlist = @exceptlist + ','--loop through listWHILE CHARINDEX(',', @exceptlist) > 0BEGIN --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, '')ENDdeclare db cursor for SELECT b.Name as TableName, c.Name as ColumnNameFROM sysobjects b, syscolumns cWHERE 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 typesorder by b.nameopen dbfetch next from db into @TableName, @ColumnNameWHILE @@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, @ColumnNameENDIF @Replace=0 SELECT * FROM #T ORDER BY TableNameDROP TABLE #TDROP TABLE #listclose dbdeallocate db[/quote]

RE: Find and Replace a String in the Whole Database

l.carpay — Wed, 26 Oct 2011 23:45:07 GMT

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

RE: Find and Replace a String in the Whole Database

David Leibowitz — Fri, 11 Apr 2008 07:18:25 GMT

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 ColumnNameFROM sys.objects b, syscolumns c, sys.schemas sWHERE C.id = b.OBJECT_ID --b.id and b.type='u' AND c.xType IN (35, 99, 167, 175, 231, 239) -- string typesAND s.SCHEMA_ID = b.schema_idorder BY b.name

Find and Replace a String in the Whole Database

Luiz-458831 — Sat, 09 Feb 2008 02:30:00 GMT

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