fun_search.sql

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER 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 SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO