SQL Global String Replace

[hofar...@houseoffusion.com]

Does anyone have an update program that can update a text string in all tables/rows/columns of an MS SQL data base?

Need to do a global text string replace on several sites. Any help appreciated.

Thanks,

Robert Harrison
Director of Interactive Services

Austin & Williams
Advertising I Branding I Digital I Direct  
125 Kennedy Drive,  Suite 100   I  Hauppauge, NY 11788
T 631.231.6600 X 119   F 631.434.7022  
http://www.austin-williams.com

Blog: http://www.austin-williams.com/blog
Twitter: http://www.twitter.com/austin_wi

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357831

[hofar...@houseoffusion.com]

you mean like this
http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/


On Tue, Mar 4, 2014 at 1:55 PM, Robert Harrison
<rob...@austin-williams.com>wrote:

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357833

[hofar...@houseoffusion.com]

Take a look at the sys.tables and sys.columns tables in your SQL database.
You should be able to write a couple of cursors to loop over each and just
print out the SQL to run separately (or you can get fancy and generate the
SQL statement and run it via EXEC sp_executeSQL functions). I don't have my
SQL server handy but can take a look later.

--
Jeff

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357834

[hofar...@houseoffusion.com]

Yes, I do mean like that, but I was really hoping someone had it already written up in CF with a tested procedure they would be willing to share.

I was able to find several downloads for PHP, but nothing for CF.

Thanks

Robert Harrison
Director of Interactive Services

Austin & Williams
Advertising I Branding I Digital I Direct  
125 Kennedy Drive,  Suite 100   I  Hauppauge, NY 11788
T 631.231.6600 X 119   F 631.434.7022  
http://www.austin-williams.com

Blog: http://www.austin-williams.com/blog
Twitter: http://www.twitter.com/austin_

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357835

[hofar...@houseoffusion.com]

Actually, that's the kind of operation that you'd not want to perform in CF
(or PHP or any other database client). Unless you truly need all that data
within a CF page for some other reason, you shouldn't be sending it all
back and forth between DBMS and CF.

--- Ben

(Sent from my newest Android device)
On Mar 4, 2014 6:08 AM, "Robert Harrison" <rob...@austin-williams.com>
wrote:

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357836

[hofar...@houseoffusion.com]

Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF.

... I'm going to do this locally, but it seems to be done in PHP as a matter of course. We use such a thing on our WordPress sites when to change the URLs we move from a staging URL to a live URL, and there are lots of PHP programs prewritten to do just that. What I'm doing here is the same thing... changing URLs embedded into the CMS pages.

- Robert

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357837

[hofar...@houseoffusion.com]

I agree with Ben...this is something that you should be running in SQL
Management Studio. ColdFusion is not meant to run this kind of stuff
(unless you set the timeout on your templates to 0). The code attached
below will loop over all of the user tables, and then loop over all of the
text type columns for that table while writing out an update statement.
Send your results to Text in the query window. Then you can cut and paste
the text results into a new query window and run it. Make sure you change
the text values to be changed and what they are going to be changed to.
Also, if your tables follow a naming convention, you can add that to the
first cursor declaration as an additional where clause...

DECLARE @tableName NVARCHAR(255), @objectID BIGINT, @columnName
NVARCHAR(255),
@TextToReplace NVARCHAR(1000), @ReplaceTextWith NVARCHAR(1000)

SET @TextToReplace = 'www.mysite.com'
SET @replaceTextWith = 'www.mynewsite.com'

DECLARE userTables CURSOR FOR
SELECT name, object_id FROM sys.tables WHERE [type] = 'U'

OPEN userTables

FETCH NEXT FROM userTables
INTO @tableName, @objectID

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'UPDATE ' + @tableName + ' SET '
/*
Select the columns from the table where the data type is a text type
column
Text - 35
sql_variant - 98
ntext - 99
varchar - 167
char - 175
nvarchar - 231
nchar - 239
*/
DECLARE userColumns CURSOR FOR
SELECT name FROM sys.columns WHERE object_id = @objectID AND
system_type_id IN (35,98,99,167,175,231,239)

OPEN userColumns

FETCH NEXT FROM userColumns
INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '[' + @columnName + '] = REPLACE([' + @columnname + '], ''' +
@TextToReplace + ''', ''' + @ReplaceTextWith + '''),'

FETCH NEXT FROM userColumns
INTO @columnName
END

CLOSE userColumns
DEALLOCATE userColumns

PRINT '1=1'
PRINT 'GO'
PRINT ' '

FETCH NEXT FROM userTables
INTO @tableName, @columnName

END
CLOSE userTables
DEALLOCATE userTables

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357847

[hofar...@houseoffusion.com]

Thanks everyone for the suggestions. I've tested the one at this link: http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/ and it works perfectly.

I mentioned previously that I was hoping to run it in CF, and that was partially because some of the hosts don't like to give direct access to the data bases on their servers. Regardless, after testing I can see it's a heavy load and have to agree with Ben Forta that it really should be run as a query in Studio. I'll deal with getting the access I need to run directly.

Thanks,
Robert

Robert Harrison
Director of Interactive Services

Austin & Williams
Advertising I Branding I Digital I Direct  
125 Kennedy Drive,  Suite 100   I  Hauppauge, NY 11788
T 631.231.6600 X 119   F 631.434.7022  
http://www.austin-williams.com

Blog: http://www.austin-williams.com/blog
Twitter: http://www.twitter.com/austi

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357865

[hofar...@houseoffusion.com]

you could just used a stored procedure which will save it directly to the
database, and then execute it from CF


On Thu, Mar 6, 2014 at 9:05 PM, Robert Harrison
<rob...@austin-williams.com>wrote:

>

> Thanks everyone for the suggestions. I've tested the one at this link:

> http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/and it works perfectly.

>
> I mentioned previously that I was hoping to run it in CF, and that was
> partially because some of the hosts don't like to give direct access to the
> data bases on their servers. Regardless, after testing I can see it's a
> heavy load and have to agree with Ben Forta that it really should be run as
> a query in Studio. I'll deal with getting the access I need to run directly.
>
> Thanks,
> Robert
>
> Robert Harrison
> Director of Interactive Services
>
> Austin & Williams
> Advertising I Branding I Digital I Direct
> 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788
> T 631.231.6600 X 119 F 631.434.7022
> http://www.austin-williams.com
>
> Blog: http://www.austin-williams.com/blog
> Twitter: http://www.twitter.com/austi
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357866

[hofar...@houseoffusion.com]

> you could just used a stored procedure which will save it directly to the database, and then execute it from CF

That never even crossed my mind. Good idea. Thanks.

Robert Harrison
Director of Interactive Services

Austin & Williams
Advertising I Branding I Digital I Direct  
125 Kennedy Drive,  Suite 100   I  Hauppauge, NY 11788
T 631.231.6600 X 119   F 631.434.7022  
http://www.austin-williams.com

Blog: http://www.austin-williams.com/blog

Twitter: http://www.twitter.com/austin_

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357867