SET NOCOUNT ON
DECLARE @stringToFind NVARCHAR(100)
DECLARE @stringToReplace NVARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand NVARCHAR(max)
DECLARE @where NVARCHAR(max)
DECLARE @columnName sysname
DECLARE @object_id INT
SET @stringToFind = 'Smith'
SET @stringToReplace = 'Jones'
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