Find and Replace a String in the Whole Database

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

  • 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

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

  • 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

  • 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 😉

  • 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.

  • 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!

  • Did you set @Replace to true (SET @Replace = 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/

  • Thanks for the script.

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

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