Jiansen Lu
SQL Server Find and Replace Values in All Tables and All Text Columns
August 6th 2008 04:51
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','v archar','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.
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','v archar','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.
| 49 |
| Vote |
Add To: 
subscribe to this blog
