Alex Duggleby's Palace of Words

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