Adapt Replace all strings in all tables to work with text

Question

I have the following script. It replaces all instances of @lookFor with @replaceWith in all tables in a database. However it doesn't work with text fields only varchar etc. Could this be easily adapted?

------------------------------------------------------------
-- 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"&gt;&lt;/script&gt;&lt;!--')
--SET @lookFor =  QUOTENAME('<script src=http://www.banner82.com/b.js&gt;&lt;/script&gt;')
--SET @lookFor =  QUOTENAME('<script src=http://www.adw95.com/b.js&gt;&lt;/script&gt;')
SET @lookFor =  QUOTENAME('<script src=http://www.script46.com/b.js&gt;&lt;/script&gt;')
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

Answer 1

You can not use REPLACE on text-fields. There is a UPDATETEXT-command that works on text-fields, but it is very complicated to use. Take a look at this article to see examples of how you can use it to replace text:

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

Answer 2

Yeah. What I ended up doing is I converted to varchar(max) on the fly, and the replace took care of the rest.

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