DECLARE @fieldtype sysname
SET @fieldtype='varchar'--删除处理
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''abc'','''')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
SET @fieldtype='varchar'--删除处理
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''abc'','''')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
还有就是我想替换,比如把"abc"替换成"newabc",而不是删除
declare table_cursor cursor for
select a.name,b.name from sysobjects a,syscolumns b
where a.iD=b.iD AnD a.xtype='u'
AnD (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
declare @str varchar(500),@str2 varchar(500)
set @str='abc'
--这里是你要替换的字符
set @str='newabc'
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;
SET @fieldtype='varchar' --删除处理
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''abc'',''newabc'')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
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'
)
declare @str varchar(500),@str2 varchar(500)
--这里是你要替换的字符
set @str='abc'
set @str='newabc'
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;
DECLARE @fieldtype sysname
SET @fieldtype='varchar' --删除处理
DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = replace(' + QUOTENAME(c.name) + ',''abc'',''newabc'')'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
我觉得可以把常见sql问题可以整理成一个chm就好了
http://forum.csdn.net/PointForum/Forum/ReplyTopic.aspx?forumID=55b4468c-6fde-444f-bde0-6e82082b1b73&topicID=c99c8cef-491f-401f-a115-40f06f408bb9&postDate=2008-6-20+21%3a17%3a46回复内容太短了!
1.建ado的连接,将所有表名在于数组中
2.通过数组逐个表用ado打开,再逐条记录,逐个字段查找所要字符串,找到后再替换,再update即可
以下为vfp的程序,其它语言类似
adocon=createobject('adocon.connection') &&创建连接
adocon.connectstring='....' &&录入连接串
adocon.open &&打开连接
adorec=creatobject('adodb.recordset')&&创建记录集
*假设表存于数组atable[n]中
for i=1 to n
lstr='select * from '+atable[i]
if adorec.active then
adorec.close
endif
adorec.open(lstr,adocon,1,3)
adorec.movefirst
do while not adorec.eof
for j=0 to adorec.fiels.count-1
&&代码略
&&步骤 查每字段的类型,如果为字符型即检查字段值,如含abc即替换字段值
endfor
adorec.update &&更新记录
adorec.movenext &&下一条记录
enddo
&&一个表处理完后,for循环进入下一表的处理,直到全部表处理完.
endfor