Sql批量替换所有表中内容

 

DECLARE @t            VARCHAR(255),
        @c            VARCHAR  (255)
 
DECLARE table_cursor           CURSOR  
FOR
    SELECT a.name,
           b                   .name
    FROM   sysobjects a,
           syscolumns          b,
           systypes            c
    WHERE  a.id = b.id
           AND a.xtype = 'u'
           AND c.name 
               IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')/* --这里如果你的text(ntext)类型没有超过8000(4000)长度，才可以使用*/ 

DECLARE @str                   VARCHAR(500),
        @str2                  VARCHAR(500)
 
SET @str = '上海坦瑞信息技术' /*这里是你要批量替换的字符*/ 
SET @str2 = '江苏新洲医疗科技' /*替换后的字符*/                 /*这里是你想批量替换成的字符*/ 
OPEN table_cursor 
 FETCH NEXT FROM table_cursor 
 INTO @t,@c 
WHILE (@@fetch_status = 0)
BEGIN
    EXEC (
             'update [' + @t + '] set [' + @c + ']=replace(cast([' + @c + 
             '] as varchar(8000)),''' + @str + ''',''' + @str2 + ''')'
         )
    
    FETCH NEXT FROM table_cursor 
    INTO @t,@c
END CLOSE table_cursor DEALLOCATE table_cursor;