Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Find and Replace Values in All Tables and All Text Columns

By:   |   Read Comments (33)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Scripts

Next Free Webcast - The more things change… DBAs versus Sysadmins in cloud availability


In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database.  I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database.  I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.


The first tip included a script that could be run to find a certain string in any text data type such as char, nchar, nvarchar, varchar, text and ntext.

The script for this tip basically follows the same premise to find the data, but takes it a step further and allows you to replace the text that is found.

The only thing that needs to change to run this script are the database where you want this to run and the values for these two parameters:

  • @stringToFind
  • @stringToReplace

DECLARE @stringToFind VARCHAR(100) 
DECLARE @stringToReplace VARCHAR(100) 
DECLARE @schema sysname 
DECLARE @table sysname 
DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @object_id INT 
SET @stringToFind = 'Smith' 
SET @stringToReplace = 'Jones' 
         A.NAME      AS TABLENAME, 
FROM     sys.objects A 
         INNER JOIN sys.schemas B 
           ON A.SCHEMA_ID = B.SCHEMA_ID 
WHERE    TYPE = 'U' 

INTO @schema, 
    FROM   sys.columns A 
           INNER JOIN sys.types B 
    WHERE  OBJECT_ID = @object_id 
           AND IS_COMPUTED = 0 
           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    INTO @columnName 
        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName 
                           + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' 
                           + @stringToFind + ''',''' + @stringToReplace + ''')' 
        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 
        EXEC( @sqlCommand + @where) 
        SET @count = @@ROWCOUNT 
        IF @count > 0 
            PRINT @sqlCommand + @where 
            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 
            PRINT '----------------------------------------------------' 
        INTO @columnName 
    INTO @schema, 

If the above is run in the AdventureWorks database as is, these are the messages that are returned.

UPDATE Person.Address SET [AddressLine1] = REPLACE(convert(nvarchar(max),[AddressLine1]),'Smith','Jones') WHERE [AddressLine1] LIKE '%Smith%'
Updated: 2
UPDATE Person.Address SET [City] = REPLACE(convert(nvarchar(max),[City]),'Smith','Jones') WHERE [City] LIKE '%Smith%'
Updated: 1
UPDATE Person.Contact SET [LastName] = REPLACE(convert(nvarchar(max),[LastName]),'Smith','Jones') WHERE [LastName] LIKE '%Smith%'
Updated: 105
UPDATE Production.ProductReview SET [ReviewerName] = REPLACE(convert(nvarchar(max),[ReviewerName]),'Smith','Jones') WHERE [ReviewerName] LIKE '%Smith%'
Updated: 1

The above shows the command that was run and how many rows were affected.  As you can see we are using the CONVERT function to convert the datatypes to nvarchar(max) prior to doing the REPLACE function.  The reason for this is that you can not use the REPLACE function against a text or ntext datatype, so we are doing a conversion prior to the change.  Although the CONVERT is not needed for char, nchar, varchar and nvarchar it was easier to just convert everything instead of having different logic, but this could be easily put in place.

If we did not use the CONVERT function we would have to use these two functions TEXTPTR and UPDATETEXT to change the data in the text and ntext columns.  This is a lot more work and therefore the approach we used is much simpler.  The downside is that this only works for SQL 2005 and later where the nvarhcar(max) datatype is supported.  In addition, this is another reason that Microsoft suggests moving away from text and ntext to varchar(max) and nvarchar(max).

One thing to note is that if your replacement text is longer than the text your are searching for you may run into issues of truncating data which is not handled in this script.

Next Steps
  • Depending on the size of your database this could take some time to run, so be careful when you run this since it will be hitting every table and every column that has one of these datatypes: char, nchar, nvarchar, varchar, text and ntext.
  • Give it a try and see how it works.  You can use the BEGIN TRAN and ROLLBACK statements to see what will be updated and than rollback the transactions.  Just be careful on large databases and production databases since this will hold locks on the UPDATEs until the rollback statement is issued.
  • If you have any tweaks to offer, please submit them to the forum post mentioned below.

Last Update:

next webcast button

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Thursday, February 22, 2018 - 7:16:41 AM - Greg Robidoux Back To Top

Hi Ferhat,

change these two lines of code in the script above as follows and see if this works for you.  You should be using single quotes instead of double quotes.

SET @stringToFind = 'ali' 
SET @stringToReplace = 'maliye' 


Thursday, February 22, 2018 - 5:36:22 AM - Ferhat Kocabaş Back To Top



My value is for example "ali" 

this code changes "maliye" values

I riding this code for solution


 SET @where = ' WHERE [' + @columnName + '] = '+ @stringToFind + ''


but, it's getting error Invalid column name 'ali'


what's wrong?


Friday, September 29, 2017 - 2:26:37 AM - Prinso Back To Top


 Excellent code... thanks!

Friday, February 10, 2017 - 1:30:28 PM - Ann Back To Top

 Thanks a lot! Saved me an hour.

Tuesday, November 29, 2016 - 9:42:58 AM - Chris Cody Back To Top

 Thank you for helping make the internet transformational.


Thursday, May 05, 2016 - 7:54:42 PM - Marc Q Back To Top


This is a brilliant script. 

It saved us hours of maintenance!

Thank you.


Tuesday, March 24, 2015 - 3:20:38 PM - Regginald Kembo Back To Top

Thanks so much for this. You've jsut saved me a lot of time deleting spam links in my DB.

Tuesday, July 29, 2014 - 1:58:52 PM - Kyle S Back To Top

We had a table named "User" so we had to change this line:


'UPDATE ' @schema '.' @table ' SET [' @columnName 


'UPDATE ' @schema '.[' @table '] SET [' @columnName  


We needed to add those [ ] characters around the table.


Wednesday, March 05, 2014 - 9:16:02 AM - Tim Back To Top

Awesome!  Thank you.

For those wanted to run this against one table, first select your DB (add USE YOURDBNAME GO to the begining of the script), and then in the first select statement towards the top, change








Sunday, July 21, 2013 - 12:05:05 AM - JasonB Back To Top

Okay. I give. How do you modify this to find/replace in a single table, rather than all of my tables across my whole db?

Thanks for the great script.

Friday, May 24, 2013 - 9:48:06 AM - FrogMeister Back To Top

This worked beautifully!  You have saved me so much time and frustration I can't thank you enough! K-

Friday, March 15, 2013 - 11:57:22 AM - Marc Back To Top

Thank you so much, this just saved me so much time - I ran it without errors on Microsoft SQL 2008 R2 to Search & Replace an email address that was in multiple tables and columns.

Thursday, January 10, 2013 - 10:05:51 AM - Greg Robidoux Back To Top

@Nicholas - no this script won't allow you to make the datatype changes.

Tuesday, January 08, 2013 - 5:47:22 PM - Nicholas Petersen Back To Top

Actually, now that I think about it, I'd probably have to drop and recreate all tables that I wanted to do that to.

Tuesday, January 08, 2013 - 5:45:09 PM - Nicholas Petersen Back To Top

Can this script be run to change the data type in all tables, for example, from varchar(max) to text?

Wednesday, November 21, 2012 - 9:47:09 AM - Greg Robidoux Back To Top

@Girish - you should be able to use the above or just use a simple REPLACE statement


Wednesday, November 21, 2012 - 7:29:03 AM - Girish Rakhe Back To Top

I have a  string  

for ex. 'Test Values ##test1## testing value ##t1##' 


Now what i want i want to replace all values from string which coming under ## values 

Monday, November 19, 2012 - 7:52:24 PM - Greg Robidoux Back To Top

@Brett - see answers below

1 - you could probably change this to VARCHAR if all of the data types are VARCHAR.  I think I did it this way to accomodate both VARCHAR and NVARCHAR.

2 - this just gets runs in the database you want to update you can just run this command

USE databaseName -- put in your database here

Monday, November 19, 2012 - 1:35:12 PM - Brett Back To Top

Sorry but I am not so familiar with SQL but this is the function I need.  I am going to run this agains an existing database from a 3rd party vendor.  I just have two questions.

1.  I don't want to convert varchar to nvarchar as I am not sure of repercussions.  Can I just delete the convert statement as all the fields I wish to update are varchar.

2.  Where is the database name changed.

Thank you, Bret


Saturday, November 03, 2012 - 5:17:42 AM - kiran Back To Top

Cool srcipt. Helped lot to clear tables affected by sql injection.

:) :)

Tuesday, August 07, 2012 - 3:27:49 AM - Ashutosh Back To Top

its showing error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Wednesday, August 01, 2012 - 11:20:12 AM - Kemre Back To Top

Great script and thanks for sharing!

Thursday, June 21, 2012 - 12:37:50 PM - Francesco Back To Top

Thanks a lot for this wonderful script!!!

Thursday, April 12, 2012 - 6:41:43 AM - Vikash Back To Top

many many thanks for this script.

Monday, March 19, 2012 - 9:40:20 PM - Scott Back To Top

I love you. You just saved me hours of work. An old, old script had an sql injection attack. What would have taken hours to clean up, is now good to go. Now I have time to go replace my old code with new stored procedures.



Saturday, October 30, 2010 - 6:30:21 AM - Sandeep Barua Back To Top

Thanks a lot of this wonder ful script SIR

You save my soul Wish u luck and keep posting more wonderful scripts like this


Thanks a lot once again

Saturday, August 14, 2010 - 2:44:12 AM - peters4oz Back To Top

 This script was a lifesaver.  Worked perfectly on a large Dot Net Nuke site that had to be migrated to another domain name where the content had many hardcoded links buried away in all sorts of tables.  Thanks a million!

Friday, July 17, 2009 - 4:51:09 AM - [email protected] Back To Top

Hi All,

I got the above Stored Procedure which is really good. But if i want same procedure but with some small changes like,

"SQL Server Find and Replace Values in a specific Tables and All Text Columns"

then how do i code it.

 Thanks for all help.

Thursday, February 05, 2009 - 6:13:11 AM - grobido Back To Top

That is correct you can not use the Replace for Text and NText for SQL 2000.

SQL 2005 makes this a lot easier than SQL 2000.

 To update data in Text and NText columns in 2000 you need to use this approach.

Here is on example: http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html


Wednesday, February 04, 2009 - 11:19:18 PM - thinkerxp Back To Top

Hi, I didn't try your approach yet, 'cause i'm using sql server 2000.

But I know REPLACE function does not work with TEXT or NTEXT field. The ntext field can be replaced with your code?


Monday, October 06, 2008 - 11:14:36 AM - jwheat Back To Top

 Ahhh, yes, I imagine it is only SQL 2000 Server.

thanks for the quick reply.

 ... back to the drawing board

Monday, October 06, 2008 - 9:13:41 AM - aprato Back To Top

 The script is SQL Server 2005 specific.  Are you running it on a SQL 2000 server?

Monday, October 06, 2008 - 7:54:30 AM - jwheat Back To Top

 This just what I'm looking for, however when I run it I get the following -

Server: Msg 208, Level 16, State 1, Line 19
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 19
Invalid object name 'sys.schemas'.

Any ideas?



Learn more about SQL Server tools