How to search and replace SQL Server data in all columns of all tables, in a given database?

This is one of the common requests I see in the newsgroups. People often wonder how they could search and replace data, that is stored in their tables. While this doesn't sound like a very good thing to do, there are some genuine situations, where 'find and replace' functionality is needed. For example, spelling mistakes in the column values, or moving a database from one location to another, and wanting to replace all location specific content.

Is there a built-in 'Global Search and Replace' functionality available in SQL Server? No. The REPLACE function provided by T-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 occurences 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 occurences of the string are replaced. Sample output would look something like this:

Outcome
----------------------------------------------------
Replaced 55 occurence(s)

Here's a word of caution, before you go ahead and 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. So, if you are trying to run it on a large database, be prepared to wait!

Another word of caution: 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. Do you?

Create this procedure in the required database and here is how call it:

--To replace all occurences of 'America' with 'USA':
EXEC SearchAndReplace 'America', 'USA'
GO

Here is the complete stored procedure code:
(Note: If you only want to search, but not replace, try this procedure instead: How to search all columns of all tables in a database for a keyword?)

CREATE PROC SearchAndReplace
(
 @SearchStr nvarchar(100),
 @ReplaceStr nvarchar(100)
)
AS
BEGIN

 -- 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
 BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    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
   BEGIN
    SET @SQL= 'UPDATE ' + @TableName + 
      ' SET ' + @ColumnName 
      + ' =  REPLACE(' + @ColumnName + ', ' 
      + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
      ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    EXEC (@SQL)
    SET @RCTR = @RCTR + @@ROWCOUNT
   END
  END 
 END

 SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END