转自CSDN--ojuju10
已知表和字段,用like去模糊查询一个字符串,这个比较简单,但是只已知一个字符串,去查询整个数据库内有那些表里面包含这个字符串,这个问题就不是那么简单啦!通过查询了大量的资料,最后用游标实现了这个需求: create proc Full_Search(@string varchar(50))
as
begindeclare @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
exec('select distinct '+@colname+' from ' +@tbname +' where '+ @colname+' like '+'''%'+@string+'%''') fetch next from colroy into @colname
end
close colroy
deallocate colroyfetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
endexec Full_Search '市场'drop proc Full_Search
已知表和字段,用like去模糊查询一个字符串,这个比较简单,但是只已知一个字符串,去查询整个数据库内有那些表里面包含这个字符串,这个问题就不是那么简单啦!通过查询了大量的资料,最后用游标实现了这个需求: create proc Full_Search(@string varchar(50))
as
begindeclare @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
exec('select distinct '+@colname+' from ' +@tbname +' where '+ @colname+' like '+'''%'+@string+'%''') fetch next from colroy into @colname
end
close colroy
deallocate colroyfetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
endexec Full_Search '市场'drop proc Full_Search
create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec('select 1 from '+@tab+' where '+@col+' like ''%社区服务受理中心%''')
goexec master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0 insert tabss (tabname,colname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'
select * from tabss