贴(邹老大的):
--搜索某个字符串在那个表的那个字段中declare @str varchar(100)
set @str='White' --要搜索的字符串declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@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/*--测试结果所在的表及字段: [authors].[au_lname]--*/
--搜索某个字符串在那个表的那个字段中declare @str varchar(100)
set @str='White' --要搜索的字符串declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@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/*--测试结果所在的表及字段: [authors].[au_lname]--*/
解决方案 »
- 关于使用GROUP BY 查询两张表的疑问
- 一段SQL返回错误,不知道什么原因
- 关于在内存中操作和在数据库中操作的速度比较!
- 对pdf文件的存储方案
- 想问问在MS-SQL中存储过程中能不能放放入循环语句
- 一个非常愚昧的问题:怎么在SQL SERVER 2000企业管理器中的一个数据库中的表复制,再粘贴到另一个数据库中?怎么只有复制,没有粘贴?谢
- 请教关于统计的问题
- 存储过程输出变量问题(先谢了)
- 请问在局域网中连接sql server2000时,为什么在odbc建立sql server数据源时,有的微机用管道命名协议能连通,而有的用tcp/ip协议连通,这两各协议在连接速度上有什么区别。
- 简单sql 查询,help me more!!!
- CHECK约束
- 如何查询用户登录及1操作信息?
--小改一下就可以了
--搜索某个字符串在那个表的那个字段中
use yourdatabase --yourdatabase填你的数据库名
go
declare @str varchar(100)
set @str='上海部门人员' --要搜索的字符串declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@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
CREATE PROCEDURE serchfield ASdeclare @str varchar(100)
set @str='2004-3-21 10:03:57 (云河许云)' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@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='云河许云' --要搜索的字符串
看看
先不忙做成存储过程
go
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'serchfield'
AND type = 'P')
DROP PROCEDURE serchfield
GO
CREATE PROCEDURE serchfield ASdeclare @str varchar(100)
set @str='Westboro' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@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
go
exec serchfield
/*
结果为:
所在的表及字段: [Territories].[TerritoryDescription]
*/
set @str=N'云河许云' --要搜索的字符串declare @s Nvarchar(4000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like N''%'+@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/*--测试结果所在的表及字段: [authors].[au_lname]--*/
create table tb(a ntext)
insert tb select '可能有些字段是ntext类型的,它可以搜出直接是“云河许云”,但''2004-3-21 10:03:57 (云河许云)''就搜不出来'
go--搜索某个字符串在那个表的那个字段中declare @str Nvarchar(100)
set @str=N'2004-3-21 10:03:57 (云河许云)' --要搜索的字符串declare @s Nvarchar(4000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like N''%'+@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.xtype in(175,239,99,35,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
go--删除测试
drop table tb/*--测试结果所在的表及字段: [tb].[a]
--*/
--原来是不允许text/ntext的,加上就行了