Adapt Replace all strings in all tables to work with text

------------------------------------------------------------
-- 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 =  QUOTENAME('"></title><script src="http://www0.douhunqn.cn/csrss/w.js"></script><!--')
--SET @lookFor =  QUOTENAME('<script src=http://www.banner82.com/b.js></script>')
--SET @lookFor =  QUOTENAME('<script src=http://www.adw95.com/b.js></script>')
SET @lookFor =  QUOTENAME('<script src=http://www.script46.com/b.js></script>')
SET @replaceWith = ''

-- 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')
--INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('text')

-- 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 VARCHAR(max)
	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

-- 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 =  ('bla')



	SET @replaceWith = ''

	-- 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','ntext','text')
	--INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('text')

	-- 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 VARCHAR(max)
		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
			PRINT 'UPDATE ' + @tblName + ' SET'
			SET @first = 1

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

				SET @first = 0

				FETCH NEXT FROM cur_columns INTO @colName
			END
			PRINT 'GO'

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

		FETCH NEXT FROM cur_tables INTO @tblName, @tblID
	END

	CLOSE cur_tables
	DEALLOCATE cur_tables