Find and Replace a String in the Whole Database

  • Luiz-458831

    SSCommitted

    Points: 1904

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

  • David Leibowitz

    SSC Veteran

    Points: 244

    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

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

  • jgoncalves

    SSC Rookie

    Points: 25

    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

  • 2903r87fggh

    SSC Veteran

    Points: 213

    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: 1807

    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

    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

    SSCommitted

    Points: 1904

    Did you set @Replace to true (SET @Replace = 1)?

  • regemail

    SSC Enthusiast

    Points: 144

    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/

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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