Replace A String From The Data In The Database

Following SQL script will help to replace a particular string from all user defined tables in the database.

DECLARE @TEMP TABLE(tabname VARCHAR(100))
DECLARE @COUNT INT, @tabname VARCHAR(100)
DECLARE @ColTemp TABLE(colName VARCHAR(100))
DECLARE @ColCount INT, @ColName VARCHAR(100)
DECLARE @SQLQuery VARCHAR(1000), @ReplaceChar VARCHAR(10), @ReplaceTo VARCHAR(10)

SET @ReplaceChar='-'
SET @ReplaceTo = '?'

INSERT INTO @TEMP (tabname)
(SELECT [name] FROm SYS.TABLES WHERE name='tblFormula')

SELECT @COUNT=Count(*) FROM @TEMP

WHILE @COUNT>0
BEGIN
SELECT TOP 1 @tabname=tabname FROM @TEMP

INSERT INTO @ColTemp (colName)
(SELECT Top 1 [name] FROm SYS.COLUMNS WHERE [Object_id]=Object_ID(@tabname))

SELECT @ColCount=Count(*) FROM @ColTemp
WHILE @COLCount>0
BEGIN
SELECT TOP 1 @ColName=ColName FROM @ColTemp

SET @SQLQuery = 'UPDATE ' + @tabname + ' SET ' + @ColName + '= REPLACE(' + @ColName + ', ''' + @ReplaceChar + ''','''+ @ReplaceTo + ''')'

PRINT @SQLQuery

DELETE FROM @ColTemp WHERE ColName=@ColName
SELECT @ColCount=Count(*) FROM @ColTemp
END

DELETE FROM @TEMP WHERE tabname=@tabname
SELECT @COUNT=Count(*) FROM @TEMP
END