SQL Server Find and Replace Values in All Tables and All Text Columns
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.
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%'
UPDATE Person.Address SET [City] = REPLACE(convert(nvarchar(max),[City]),'Smith','Jones') WHERE [City] LIKE '%Smith%'
UPDATE Person.Contact SET [LastName] = REPLACE(convert(nvarchar(max),[LastName]),'Smith','Jones') WHERE [LastName] LIKE '%Smith%'
UPDATE Production.ProductReview SET [ReviewerName] = REPLACE(convert(nvarchar(max),[ReviewerName]),'Smith','Jones') WHERE [ReviewerName] LIKE '%Smith%'
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.
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.