Alex Duggleby’s Palace of Words

Have you ever been asked to write something where you knew from the start that it could posisbly 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: 08.05.2008
--
-- 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 = 'serverOld'
SET @replaceWith = 'serverNew'

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