1.如果只知道一个字段的值,想通过这个值查询它属于哪个表的哪个字段
declare @str varchar(100)
set @str='abcd' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
/*
注意：
1.字段值完全相等：
select 1 from ['+b.name+'] where ['+a.name+'] ='''+@str+'''
2.所搜索的字段值为实字段值的一部分
select 1 from ['+b.name+'] where ['+a.name+'] =''%'+@str+'%''
*/
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] ='''+@str+''')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
2. 修改字段值:
将已知数据库中字段值为'abc'的值,全部替换为'abca'declare @oldstr varchar(100)
set @oldstr='abc' --原字符
declare @newstr varchar(100)
set @newstr='abca' --新字符
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] = '''+@oldstr+''')
update ['+b.name+'] set ['+a.name+']='''+@newstr+''' where ['+a.name+']='''+@oldstr+''' '
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
转自: http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
文章来源于 http://www.cnblogs.com/zhangzs8896 版权归原作者所有
declare @str varchar(100)set @str='abcd' --要搜索的字符串declare @s varchar(8000)declare tb cursor local for/*
注意： 1.字段值完全相等： select 1 from ['+b.name+'] where ['+a.name+'] ='''+@str+''' 2.所搜索的字段值为实字段值的一部分 select 1 from ['+b.name+'] where ['+a.name+'] =''%'+@str+'%''*/select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] ='''+@str+''') print ''所在的表及字段: ['+b.name+'].['+a.name+']'''from syscolumns a join sysobjects b on a.id=b.idwhere b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167)open tbfetch next from tb into @swhile @@fetch_status=0begin exec(@s) fetch next from tb into @sendclose tbdeallocate tb2. 修改字段值:
将已知数据库中字段值为'abc'的值,全部替换为'abca'
declare @oldstr varchar(100)set @oldstr='abc' --原字符declare @newstr varchar(100)set @newstr='abca' --新字符declare @s varchar(8000)declare tb cursor local forselect s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] = '''+@oldstr+''')update ['+b.name+'] set ['+a.name+']='''+@newstr+''' where ['+a.name+']='''+@oldstr+''' 'from syscolumns a join sysobjects b on a.id=b.idwhere b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167)open tbfetch next from tb into @swhile @@fetch_status=0begin exec(@s) fetch next from tb into @sendclose tbdeallocate tb转自: http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
文章来源于 http://www.cnblogs.com/zhangzs8896 版权归原作者所有
