By: Greg Robidoux | Comments (34) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Scripts
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.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
