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:

Outcome
----------------------------------------------------
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
(
    @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

Call the procedure as such, for example replace all occurences of 'America' with 'USA'.

EXEC SearchAndReplace 'America', 'USA'
GO