Create Proc SearchAndReplace
(
    @SearchStr nVarChar(100),
    @ReplaceStr nVarChar(100)
)
As
Begin
    Set NoCount On

    Declare @TableName  nVarChar(256), 
            @ColumnName nVarChar(128), 
            @SearchStr2 nVarChar(110), 
            @SQL        nVarChar(4000),
            @RCTR       Int

    Set @TableName  =   ''
    Set @SearchStr2 =   QuoteName('%' + @SearchStr + '%','''')
    Set @RCTR       =   0

    While @TableName Is Not Null
    Begin
        Set @ColumnName = ''
        Set @TableName  = 
        (
            Select  Min(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME))
            From    INFORMATION_SCHEMA.Tables
            Where   Table_TYPE = 'BASE Table'
            And     QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) > @TableName
            And     ObjectProperty  (
                                        Object_Id(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME)), 
                                        'IsMSShipped'
                                     ) = 0
        )

        While (@TableName Is Not Null) And (@ColumnName Is Not Null)
        Begin
            Set @ColumnName =
            (
                Select  Min(QuoteName(COLUMN_NAME))
                From    INFORMATION_SCHEMA.COLUMNS
                Where   TABLE_SCHEMA    =   PARSENAME(@TableName, 2)
                And     TABLE_NAME      =   PARSENAME(@TableName, 1)
                And     DATA_TYPE       IN  ('char', 'VarChar', 'nchar', 'nVarChar')
                And     QuoteName(COLUMN_NAME) > @ColumnName
            )
    
            If @ColumnName Is Not Null
            Begin
                Set @SQL    =   'Update ' + @TableName + 
                                    ' Set ' + @ColumnName + ' = ' +
                                        'Replace(' + @ColumnName + ', ' + QuoteName(@SearchStr, '''') + ', ' + QuoteName(@ReplaceStr, '''') + ') ' +
                                'Where ' + @ColumnName + ' Like ' + @SearchStr2
                Exec (@SQL)

                Set @RCTR = @RCTR + @@ROWCOUNT
            End
        End    
    End

    Select 'Replaced ' + CAST(@RCTR As VarChar) + ' occurence(s)' As 'Outcome'

End

Stored Procedure for search and replace a string in all fields of all tables of one DataBase

SQL Searh And Replace in ALL DataBase

search, replace

SQL