万能模糊查询SQL
浏览数：28 /
时间：2015年06月12日
******************************************************************
* 功能：万能模糊查询SQL
* 时间：2015/1/30 16:00:22
* 开发者：流浪的菜鸟
* 需求来源：要查询系统数据库中，有某些特殊字段是否存在。
* 实现思路
1.为了防止后期，需求变更，自己打算写一个万能通用的SQL，只需要给出 关键字，就能查询到当前数据库中所有关键字存在的地方
2.第一步，要循环当前数据中所有的表，当然想到了用系统表和游标。同时在循环表的时候，嵌套循环当前表的结构，
所以必须会用到两个嵌套的游标来完成实现
3.第二步，查询相应的数据库文档，找到相应能快速查询到所有表，所有列的系统表
4.第三步，根据实现的思路，一步步的去实现完成
* 最终导出生成对应的报表，报表能看到，当前数据库中有哪些表，哪个字段的值包含了当前特殊字段
* 课外资料（必须要知道的那些潜规则）
1. sql 常见的系统表，以及系统表对应的功能查询
select * from sys.all_objects where type=‘u‘ ---查询当前数据库中所有的表，其中是属于用户创建的表，不是系统表
select * from information_schema.columns ---查询当前表中所有列，以及列的属性（比如列的名称，列的数据类型，后面有用到）
2. 游标的工作执行原理，以及相关的特性
3.存储过程，以及sql字符串的拼接操作
4.调试使用好 print 关键字，打印出来相应的sql语句来验证自己的思路和结果是否正确
*注意事项
1.自己所写的程序必须要严格优化你的SQL，我一开始没有优化，导致执行我的这个sql的时候数据库出现相应的异常，让我好蛋疼，于是我猜想
异常：由于这个系统有1589张表，循环表就循环1589次，然后再1589次当中还得循环每个表的字段（平均每个表有35个字段），
那算起来大概要循环五万次，然后这五万次循环的时候 还得去模糊拼接查询一次数据库，估计游标受不了，或者内存不足，具体原因待查
2.细节注意的地方： exec sp_executesql 其中这个地方要注意，执行这个的时候 它拼接生成的sql的标量声明必须是 nvarchar,不能是varchar,
这一点比较恶心，我这里就是少写了个n，导致一直报错。
3.循环所有的表，所有的字段，逻辑是这样的，但是实际业务没必要去这样做，这里就做好优化的相关代码
比如我这里优化了两个：
第一. 排除了数据库中表的数据是0的，也就是说当前表中没有任何数据，没有数据的我就没有必要去循环当前表的结构和模糊查询了
第二. 模糊查询肯定是针对表中字段是 字符串类型的，如果不是字符串类型的(text,varchar,nvarchar等)，当然没必要去循环模糊查询了
第三. 后期还需要优化的代码
******************************************************************/
declare @QueryKeyword nvarchar(2000)
set @QueryKeyword=‘关键字‘
declare @tblname nvarchar(200) --表名称
declare @tablenum int ---获取执行查询表中是否有数据 返回的结果 只要大于1 代表 这个表有数据，就进行列的循环读取和模糊匹配
declare @tablestr nvarchar(4000)
declare tbl_cursor cursor for select name from sys.all_objects where type=‘u‘
---定义表的游标，并且指定游标的循环范围 当前数据库的所有表(所有用户表)
---获取当前数据库中的表
open tbl_cursor --打开游标
fetch next from tbl_cursor INTO @tblname --游标开始循环读取，一条条读取，读取每一条的时候 将读到的表名称 赋值给标量@tblname
--将当前游标读取到的表名称 保存到标量中，给下面的循环使用 每一次循环得到表名
while @@FETCH_STATUS = 0 ---只要读到数据，就循环执行下面的操作
begin
----开始 排除没有数据的表 （优化）
set @tablestr=‘select @b=count(*) from ‘+@tblname
exec sp_executesql @tablestr,N‘@b int output‘,@tablenum output ---执行当前的数据结果集，有数据的表，才能进行表结构中每个字段的循环
if(@tablenum>0) --得到有数据的表
begin
declare @str nvarchar(4000)
declare @colvaluesql nvarchar(4000)
set @str=‘‘ --开始拼接 表中的字段
declare @data_typevalue nvarchar(200)
declare @num int
declare @colname nvarchar(200) ---表中列的标量
declare col_cursor cursor for select column_name,data_type from information_schema.columns where table_name= @tblname order by column_name
--定义列的游标，循环的范围是当前表中的所有列,以及列相应的数据类型
--如果不是字符串类型的 都忽略掉
open col_cursor --打开游标
fetch next from col_cursor into @colname,@data_typevalue --将列游标读到的列 放到变量中
while @@FETCH_STATUS = 0
begin
---初步判断 数据类型符合的有 nvarchar ntext 两种数据类型的 符合
--当只有数据类型符合规则的时候 才生成对应模糊查询的sql
if(@data_typevalue=‘nvarchar‘ or @data_typevalue=‘ntext‘)
begin
set @str=‘select @a=count(*) from ‘+@tblname+‘ where ‘+@colname+‘ like ‘‘%‘+@QueryKeyword+‘%‘‘‘
set @colvaluesql=‘select ‘+@colname+‘,* from ‘+@tblname+‘ where ‘+@colname+‘ like ‘‘%‘+@QueryKeyword+‘%‘‘‘
exec sp_executesql @str,N‘@a int output‘,@num output ---执行模糊查询 返回模糊查询的结果
if(@num>0) ---如果模糊查询的结果 有值 那么就说明该表这的这个字段中包括这个 关键字
begin
-------这里 可以进行相应的扩展，比如生成批量的查询 修改，删除语句,并且执行
-------扩展区
-------扩展区
print @colvaluesql ---打印出 符合模糊查询的表 生成相应的sql
exec(@colvaluesql) ----执行 sql
end
end
fetch next from col_cursor into @colname,@data_typevalue
END
close col_cursor ---关闭列的游标
deallocate col_cursor --释放列的游标
end
------结束 排除没有数据的表 （优化）
fetch next from tbl_cursor into @tblname --让表的游标继续读下一条数据
End
close tbl_cursor --关闭游标
deallocate tbl_cursor --释放游标资源
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
