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

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