Off-Topic: T-SQL: Replace all occurrences in all columns in all tables?

Update: You can download the sql file here: http://cid-8e9963932ac1f048.skydrive.live.com/self.aspx/%C3%96ffentlich/STRING|_REPLACER|_V1.2.sql (saves you cleaning up the copy-pasted code from below… oh and hello experts-exchange-users)

Have you ever been asked to write something where you knew from the start that it could possibly wreak havok? Well I did today and since I haven’t posted in a while I thought I’d quickly share. Don’t worry I’ll be back blogging Sync, MVC and more soon (and hopefully tell you why I was gone).

Without talking too much about the real scenario that this was needed for, let me just say there is a large database that stores configuration data including a set of connection strings in multiple places spread across numerous tables. And our admins moved the database but didn’t know where exactly to change these config strings. I was the only dev left in the office at the time and they turned to me asking me if I could write a script that “replaced all occurrences of stringA with stringB throughout the whole database”.

Yikes. It sounds like trouble, you know it’s gonna hurt and I don’t want to be anywhere nearby when they press F5 to run the script. Ok, nevertheless I put together a little script that did just that, sent it to them and let’s see what happens next week. They have backups so even if everything goes wrong we can quickly restore things.

Here’s what I came up with. It’s not rocket science, but does the job. Use the script at your own risk (I didn’t find anything comparable on the web so maybe it will come in handy to someone). It shows some interesting usage of the system tables that I use now and again for things like this (when I don’t have my beloved SQLSMO). Have fun and use with caution (I can’t say that enough!)

------------------------------------------------------------
-- Name: STRING REPLACER
-- Author: ADUGGLEBY
-- Version: 20.05.2008 (1.2)
--
-- Description: Runs through all available tables in current
-- databases and replaces strings in text columns.
------------------------------------------------------------

-- PREPARE
SET NOCOUNT ON

-- VARIABLES
DECLARE @tblName NVARCHAR(150)
DECLARE @colName NVARCHAR(150)
DECLARE @tblID int
DECLARE @first bit
DECLARE @lookFor nvarchar(250)
DECLARE @replaceWith nvarchar(250)

-- CHANGE PARAMETERS
SET @lookFor = 'virtual2'
SET @replaceWith = 'virtual3'

-- TEXT VALUE DATA TYPES
DECLARE @supportedTypes TABLE ( xtype NVARCHAR(20) )
INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('varchar','char','nvarchar','nchar','xml')

-- ALL USER TABLES
DECLARE cur_tables CURSOR FOR
SELECT SO.name, SO.id FROM SYSOBJECTS SO WHERE XTYPE='U'
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @tblName, @tblID

WHILE @@FETCH_STATUS = 0
BEGIN
	-------------------------------------------------------------------------------------------
	-- START INNER LOOP - All text columns, generate statement
	-------------------------------------------------------------------------------------------
	DECLARE @temp NVARCHAR(4000)
	DECLARE @count INT
	SELECT @count = COUNT(name) FROM SYSCOLUMNS WHERE ID = @tblID AND
		XTYPE IN (SELECT xtype FROM @supportedTypes)

	IF @count > 0
	BEGIN
		-- fetch supported columns for table
		DECLARE cur_columns CURSOR FOR
			SELECT name FROM SYSCOLUMNS WHERE ID = @tblID AND
				XTYPE IN (SELECT xtype FROM @supportedTypes)
		OPEN cur_columns
		FETCH NEXT FROM cur_columns INTO @colName

		-- generate opening UPDATE cmd
		SET @temp = '
	PRINT ''Replacing ' + @tblName + '''

	UPDATE ' + @tblName + ' SET
		'
		SET @first = 1

		-- loop through columns and create replaces
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF (@first=0) SET @temp = @temp  + ',
		'
			SET @temp = @temp  + @colName
			SET @temp  = @temp  + ' = REPLACE(' +  @colName + ','''
			SET @temp  = @temp  + @lookFor
			SET @temp  = @temp  + ''','''
			SET @temp  = @temp  + @replaceWith
			SET @temp  = @temp  +  ''')'

			SET @first = 0
			FETCH NEXT FROM cur_columns INTO @colName
		END

		PRINT @temp

		CLOSE cur_columns
		DEALLOCATE cur_columns
	END
	-------------------------------------------------------------------------------------------
	-- END INNER
	-------------------------------------------------------------------------------------------

	FETCH NEXT FROM cur_tables INTO @tblName, @tblID
END

CLOSE cur_tables
DEALLOCATE cur_tables

About these ads

6 thoughts on “Off-Topic: T-SQL: Replace all occurrences in all columns in all tables?

  1. Thanks for posting the script!

    I had to change line 69 to remove the quotes around the column as the first parameter to the Replace function:

    SET @temp = @temp + ‘ = REPLACE(‘ + @colName + ‘,”’

    Otherwise the column name would show up in the field after running the proc.

    I also found out out that the output of the script for one of my tables was being truncated… probably a limitation of the NVARCHAR(4000) size. Because I only needed to update specific columns, I was able to manually edit the script.

  2. Hi Mike,

    thanks, you are absolutely right. Must have crept into the script while making it ‘bloggable’ :)

    The 4000 is a bit of a limit, but if I remember right I can’t concat nvarchar(max) values, so I had to do it that way.

    Thanks,
    Alex

  3. Did not work for me. I was trying to replace a script tag that came in from somewhere and though I got no errors after running the script, the string I required to replace was not replaced.

  4. Hi Raj,

    is it possible that the field you are using is a NTEXT or TEXT field? The script will not work on those…

    (same goes for any NVARCHAR(MAX) or fields > 4000 length)

    Bye,
    Alex

  5. Hi Alex,
    Just wanted to thank you for posting your SQL code. Our database was injected with malicious scripts and your code helped me clean up hundreds of thousands of records.

    You saved my day! :)
    Anja

  6. Alex,

    This is awesome! Thanks so much for helping me finish my cleanup, I did the really important stuff semi-manually yesterday. Finding this was a life/time saver.

    Experts-Exchange should throw you some dough, this is the best solution.

    -Scott

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s