Find specific word in all rows in MS SQL database and eventually replace it

Is there a way to scan all tables in MS SQL 2008 R2 Database and 开发者_如何学Goreplace one word to another? Or if replace is not possible maybe just possibility to list all rows with specific word (and corresponding table next to it for reference)?

If it's not possible to do purely in SQL then I can use C# as well.

There is no "out of the box" solution for this, but it's not very hard to write a stored procedure that does this.

For example, the procedure below will loop over all the tables and then loop over all the columns of type varchar and nvarchar and replace the string @value with @newvalue. This is just a proof of concept and can be enhanced greatly to make it faster by adding a where clause that checks if the string contains the value for example. (with LIKE or using full text indexes).

create proc ReplaceStrings(
        @value nvarchar(maX)
      , @newvalue nvarchar(max)
      )
AS
declare @table_id int
      , @name sysname
      , @fieldname sysname
      , @sql nvarchar(max)
      , @fields nvarchar(max)

if @value = ''
  begin
      raiserror('The search value can not be empty', 16, 1) 
      return (-1)
  end    
declare tab cursor read_only local
for
select object_id, name from sys.tables

open tab
fetch next from tab into @table_id, @name

while @@FETCH_STATUS = 0 
  begin

    SELECT @sql = N'UPDATE ' + QUOTENAME(@name) + ' 
                set '  
         , @fields = NULL
    declare field cursor read_only local
    for 
    select name from sys.columns where object_id = @table_id and system_type_id in (type_id('varchar'), type_id('nvarchar'))
    open field
    fetch next from field into @fieldname
    while @@FETCH_STATUS = 0
      begin
        set @fields = coalesce(@fields + ',', '') + N' ' + quotename(@fieldname) + '  = REPLACE(' + quotename(@fieldname) + ', @value, @newvalue)' 
        fetch next from field into @fieldname        
      end
    close field
    deallocate field

    set @sql += @fields 

    print @sql
    exec sp_executesql @sql , N'@value nvarchar(max), @newvalue nvarchar(max)', @value, @newvalue

    fetch next from tab into @table_id, @name
  end
close tab
deallocate tab

return (0)

Call the procedure like this:

exec ReplaceStrings 'haha', 'hihi'