Search and Replace SQL Server data in all columns, of all tables
Last updated: October 29th '05 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

See also:   Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools SQL Server security best practices SQL Server administration best practices

Related books:

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

Note: Information & code samples from this article are tested on SQL Server 2005 RTM (Yukon) and found to be working. Will update the article in case of any compatibility issues.

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