T-SQL Find and Replace

SQL Server Find and Replace Values in All Tables and All Text Columns

Problem
In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database. I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database. I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.

Solution
The first tip included a script that could be run to find a certain string in any text data type such as char, nchar, nvarchar, varchar, text and ntext.

The script for this tip basically follows the same premise to find the data, but takes it a step further and allows you to replace the text that is found.

The only thing that needs to change to run this script are the database where you want this to run and the values for these two parameters:

@stringToFind 
@stringToReplace
SET NOCOUNT ON 

DECLARE @stringToFind VARCHAR(100) 
DECLARE @stringToReplace VARCHAR(100) 
DECLARE @schema sysname 
DECLARE @table sysname 
DECLARE @count INT 
DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @object_id INT 
                     
SET @stringToFind = 'Smith' 
SET @stringToReplace = 'Jones' 
                        
DECLARE TAB_CURSOR CURSOR  FOR 
SELECT   B.NAME      AS SCHEMANAME, 
         A.NAME      AS TABLENAME, 
         A.OBJECT_ID 
FROM     sys.objects A 
         INNER JOIN sys.schemas B 
           ON A.SCHEMA_ID = B.SCHEMA_ID 
WHERE    TYPE = 'U' 
ORDER BY 1 
          
OPEN TAB_CURSOR 

FETCH NEXT FROM TAB_CURSOR 
INTO @schema, 
     @table, 
     @object_id 
      
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    DECLARE COL_CURSOR CURSOR FOR 
    SELECT A.NAME 
    FROM   sys.columns A 
           INNER JOIN sys.types B 
             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID 
    WHERE  OBJECT_ID = @object_id 
           AND IS_COMPUTED = 0 
           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    OPEN COL_CURSOR 
     
    FETCH NEXT FROM COL_CURSOR 
    INTO @columnName 
     
    WHILE @@FETCH_STATUS = 0 
      BEGIN 
        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')' 
         
        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 
         
        EXEC( @sqlCommand + @where) 
         
        SET @count = @@ROWCOUNT 
         
        IF @count > 0 
          BEGIN 
            PRINT @sqlCommand + @where 
            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 
            PRINT '----------------------------------------------------' 
          END 
         
        FETCH NEXT FROM COL_CURSOR 
        INTO @columnName 
      END 
     
    CLOSE COL_CURSOR 
    DEALLOCATE COL_CURSOR 
     
    FETCH NEXT FROM TAB_CURSOR 
    INTO @schema, 
         @table, 
         @object_id 
  END 
   
CLOSE TAB_CURSOR 
DEALLOCATE TAB_CURSOR  

If the above is run in the AdventureWorks database as is, these are the messages that are returned.

UPDATE Person.Address SET [AddressLine1] = REPLACE(convert(nvarchar(max),[AddressLine1]),'Smith','Jones') WHERE [AddressLine1] LIKE '%Smith%'

Updated: 2
—————————————————-

UPDATE Person.Address SET [City] = REPLACE(convert(nvarchar(max),[City]),'Smith','Jones') WHERE [City] LIKE '%Smith%'

Updated: 1
—————————————————-

UPDATE Person.Contact SET [LastName] = REPLACE(convert(nvarchar(max),[LastName]),'Smith','Jones') WHERE [LastName] LIKE '%Smith%'

Updated: 105
—————————————————-

UPDATE Production.ProductReview SET [ReviewerName] = REPLACE(convert(nvarchar(max),[ReviewerName]),'Smith','Jones') WHERE [ReviewerName] LIKE '%Smith%'

Updated: 1
—————————————————-

The above shows the command that was run and how many rows were affected. As you can see we are using the CONVERT function to convert the datatypes to nvarchar(max) prior to doing the REPLACE function. The reason for this is that you can not use the REPLACE function against a text or ntext datatype, so we are doing a conversion prior to the change. Although the CONVERT is not needed for char, nchar, varchar and nvarchar it was easier to just convert everything instead of having different logic, but this could be easily put in place.

If we did not use the CONVERT function we would have to use these two functions TEXTPTR and UPDATETEXT to change the data in the text and ntext columns. This is a lot more work and therefore the approach we used is much simpler. The downside is that this only works for SQL 2005 and later where the nvarhcar(max) datatype is supported. In addition, this is another reason that Microsoft suggests moving away from text and ntext to varchar(max) and nvarchar(max).

One thing to note is that if your replacement text is longer than the text your are searching for you may run into issues of truncating data which is not handled in this script.

Next Steps

Depending on the size of your database this could take some time to run, so be careful when you run this since it will be hitting every table and every column that has one of these datatypes: char, nchar, nvarchar, varchar, text and ntext.
Give it a try and see how it works. You can use the BEGIN TRAN and ROLLBACK statements to see what will be updated and than rollback the transactions. Just be careful on large databases and production databases since this will hold locks on the UPDATEs until the rollback statement is issued.
If you have any tweaks to offer, please submit them to the forum post mentioned below.

发表评论