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?

  -Jon

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

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

thanks for the quick reply.

 ... back to the drawing board

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?

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

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.

 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!

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

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.

Cheers!

many many thanks for this script.

Thanks a lot for this wonderful script!!!

Great script and thanks for sharing!

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.

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

:) :)

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

@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
GO

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 

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

REPLACE(@string,'##test1##','RealValue')

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

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

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

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.

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