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
_syncFilter.SubdirectoryExcludes.Add("DoNotCopy" 
