Information and code samples from this article are tested on SQL Server 2005 and found to be working.
This article explains how to search and replace SQL Server data in all columns of all tables in a given database. While this doesn't sound like a very good thing to do, there are some genuine situations, where find and replace functionality is needed. If a SQL injection has gone unnoticed for some time or you have a very active database and don't want to lose any data by restoring to a date prior to the injection, a find and replace is the way to go.
The built-in REPLACE function provided by SQL can replace a given string in a string variable or a column, but you cannot directly use REPLACE function to loop through all character columns of all tables, to globally replace a string. This needs some programming effort.
Here's a stored procedure named, SearchAndReplace, that searches through all the character columns of all tables in the current database, and replaces the given string with another user provided string. It accepts a search string and a replace string as input parameters, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database and replaces all occurrences of the search string with the replace string.
Make sure you have SELECT and UPDATE permissions on all the tables involved, before you run this stored procedure. If you login as a sysadmin or a dbo, you'll be able to search and replace data from tables owned by all the users.
Feel free to extend this procedure to search other datatypes or selected tables or selected columns.
The output of this stored procedure shows you how many occurrences of the string are replaced. Sample output would look something like this:
Replaced 55 occurence(s)
A word of caution before you run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a larger database with too many character columns and a huge number of rows.
Make sure only the right people have access to this stored procedure, if you do create it on a production database. You don't want someone to just replace or overwrite your production data with junk.
Create this procedure in the required database.
CREATE PROC SearchAndReplace
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
SET @ColumnName = ''
SET @TableName =
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
) = 0
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
SET @ColumnName =
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
IF @ColumnName IS NOT NULL
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
SET @RCTR = @RCTR + @@ROWCOUNT
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
Call the procedure as such, for example replace all occurences of 'America' with 'USA'.
EXEC SearchAndReplace 'America', 'USA'
Article ID: 74, Created On: 8/20/2008, Modified: 8/20/2008