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 版权归原作者所有

































2. 修改字段值:
将已知数据库中字段值为'abc'的值,全部替换为'abca'





















转自: http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
文章来源于 http://www.cnblogs.com/zhangzs8896 版权归原作者所有