sql - 查询所有表中包含指定值
可以直接创建sql语句:
CREATE TABLE qResults (tName nvarchar(370), cname nvarchar(3630),[count] int) declare @tname nvarchar(200) declare @cname nvarchar(200) declare @countOut nvarchar(200) declare @sql nvarchar(max) declare c_search cursor for select t.name,c.name from sysobjects t inner join syscolumns c on t.id=c.id where t.type=‘u‘ and c.xtype in(56,167,175,231,239) order by t.name --xtype 这个地方限制类型为int varchar char nvarchar nchar 五种类型 open c_search fetch next from c_search into @tname,@cname while @@FETCH_STATUS=0 begin set @sql=N‘select @countx=COUNT(*) from ‘+@tname +‘ where [‘ +@cname +‘] =‘‘44‘‘ ‘ print @sql EXECUTE sp_executesql @sql,N‘@countx nvarchar(200) out ‘, @countx=@countOUT OUT insert into qResults values (@tname,@cname,@countOUT) fetch next from c_search into @tname,@cname end select tName ‘Table‘,cname ‘Field‘ from qResults where [count]>0 order by tname close c_search deallocate c_search drop table qResults
或者先创建存储过程:
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
然后execute,输入指定值即可.
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。