<p>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?</p>

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

[{'answer_id': 39699, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n\n<p><a href="http://www.sqlteam.com/article/search-and-replace-in-a-text-column" rel="nofollow noreferrer">http://www.sqlteam.com/article/search-and-replace-in-a-text-column</a></p>\n'}, {'answer_id': 39790, 'author': 'svandragt', 'author_id': 997, 'author_profile': 'https://Stackoverflow.com/users/997', 'pm_score': 3, 'selected': True, 'text': "<p>Yeah. What I ended up doing is I converted to varchar(max) on the fly, and the replace took care of the rest.</p>\n\n<pre><code>    -- PREPARE\n    SET NOCOUNT ON\n\n    -- VARIABLES\n    DECLARE @tblName NVARCHAR(150)\n    DECLARE @colName NVARCHAR(150)\n    DECLARE @tblID int\n    DECLARE @first bit\n    DECLARE @lookFor nvarchar(250)\n    DECLARE @replaceWith nvarchar(250)\n\n-- CHANGE PARAMETERS\nSET @lookFor =  ('bla')\n\n\n\n    SET @replaceWith = ''\n\n    -- TEXT VALUE DATA TYPES\n    DECLARE @supportedTypes TABLE ( xtype NVARCHAR(20) )\n    INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('varchar','char','nvarchar','nchar','xml','ntext','text')\n    --INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('text')\n\n    -- ALL USER TABLES\n    DECLARE cur_tables CURSOR FOR \n    SELECT SO.name, SO.id FROM SYSOBJECTS SO WHERE XTYPE='U'\n    OPEN cur_tables\n    FETCH NEXT FROM cur_tables INTO @tblName, @tblID\n\n    WHILE @@FETCH_STATUS = 0\n    BEGIN\n        -------------------------------------------------------------------------------------------\n        -- START INNER LOOP - All text columns, generate statement\n        -------------------------------------------------------------------------------------------\n        DECLARE @temp VARCHAR(max)\n        DECLARE @count INT\n        SELECT @count = COUNT(name) FROM SYSCOLUMNS WHERE ID = @tblID AND \n            XTYPE IN (SELECT xtype FROM @supportedTypes)\n\n        IF @count &gt; 0\n        BEGIN\n            -- fetch supported columns for table\n            DECLARE cur_columns CURSOR FOR \n                SELECT name FROM SYSCOLUMNS WHERE ID = @tblID AND \n                    XTYPE IN (SELECT xtype FROM @supportedTypes)\n            OPEN cur_columns\n            FETCH NEXT FROM cur_columns INTO @colName\n\n            -- generate opening UPDATE cmd\n            PRINT 'UPDATE ' + @tblName + ' SET'\n            SET @first = 1\n\n            -- loop through columns and create replaces\n            WHILE @@FETCH_STATUS = 0\n            BEGIN\n                IF (@first=0) PRINT ','\n                PRINT @colName +\n                    ' = REPLACE(convert(nvarchar(max),' +  @colName + '),''' + @lookFor +\n                    ''',''' + @replaceWith + ''')'\n\n                SET @first = 0\n\n                FETCH NEXT FROM cur_columns INTO @colName\n            END\n            PRINT 'GO'\n\n            CLOSE cur_columns\n            DEALLOCATE cur_columns\n        END\n        ------------------------------------------------------------------------------------------- \n        -- END INNER\n        -------------------------------------------------------------------------------------------\n\n        FETCH NEXT FROM cur_tables INTO @tblName, @tblID\n    END\n\n    CLOSE cur_tables\n    DEALLOCATE cur_tables\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/39674', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/997/']