笨小孩做开发

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

SQL全站搜索


create proc
Full_Search(@string varchar(50))
as
begin

declare @tbname
varchar(50)
declare tbroy cursor for select name from sysobjects
where
xtype= 'u ' --第一个游标遍历所有的表

open tbroy
fetch next from tbroy into
@tbname
while @@fetch_status=0
begin

declare @colname
varchar(50)
declare colroy cursor for select name from syscolumns
where
id=object_id(@tbname) and xtype in (
select xtype from systypes
where
name in ( 'varchar ', 'nvarchar ', 'char ', 'nchar ') --数据类型为字符型的字段
)
--第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段

open colroy
fetch next from colroy
into @colname
while @@fetch_status=0
begin

declare @sql
nvarchar(1000),@j int
select @sql= 'select @i=count(1) from ' +@tbname + '
where '+ @colname+ ' like '+ '''%'+@string+ '%'''
exec sp_executesql
@sql,N'@i int output',@i=@j output --输出满足条件表的记录数
if @j> 0
BEGIN

select 包含字串的表名=@tbname
--exec( 'select distinct '+@colname+' from '
+@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')
END

fetch next from colroy into @colname
end

close colroy

deallocate colroy

fetch next from tbroy into @tbname
end

close tbroy
deallocate tbroy
end
go

exec Full_Search
'搜索关键字'
posted on 2013-08-15 17:55  笨小孩做开发  阅读(323)  评论(0编辑  收藏  举报