My picture album
My code library
Search my site
SQL Server Articles New
NEW!!! Subscribe to my
|Want to keep in touch with the
latest in SQL Server world? Email firstname.lastname@example.org with
'subscribe' in the subject line
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:
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'
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
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'