数据库所有表替换所有列的特定字符串


DECLARE
@TableName VARCHAR(119) DECLARE @TableID INT DECLARE @ColName VARCHAR(111) DECLARE Table_Cursor CURSOR FOR SELECT [name],id FROM sysobjects WHERE xtype='U'; OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @TableName,@TableID; WHILE(@@FETCH_STATUS=0) BEGIN DECLARE Columns_Cursor CURSOR FOR select [name] from [syscolumns] where [id] = @TableID OPEN Columns_Cursor; FETCH NEXT FROM Columns_Cursor INTO @ColName; WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE '+@TableName+' SET '+@ColName+'=replace('+@ColName+',''lijinsm.cn'',''sdgow.cn'')'); EXEC('UPDATE '+@TableName+' SET '+@ColName+'=replace('+@ColName+',''400-111-1111'',''400-888-8888'')'); FETCH NEXT FROM Columns_Cursor INTO @ColName; END CLOSE Columns_Cursor; DEALLOCATE Columns_Cursor; FETCH NEXT FROM Table_Cursor INTO @TableName,@TableID; END CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

 

 

posted @ 2014-07-05 11:01  顿金  阅读(527)  评论(0编辑  收藏  举报