******************************************************************

* 功能：萬能模糊查詢SQL

* 時間：2015/1/30 16:00:22

* 開發(fā)者：流浪的菜鳥

* 需求來源：要查詢系統(tǒng)數(shù)據(jù)庫中，有某些特殊字段是否存在。

* 實現(xiàn)思路

1.為了防止后期，需求變更，自己打算寫一個萬能通用的SQL，只需要給出 關(guān)鍵字，就能查詢到當(dāng)前數(shù)據(jù)庫中所有關(guān)鍵字存在的地方

2.第一步，要循環(huán)當(dāng)前數(shù)據(jù)中所有的表，當(dāng)然想到了用系統(tǒng)表和游標(biāo)。同時在循環(huán)表的時候，嵌套循環(huán)當(dāng)前表的結(jié)構(gòu)，

所以必須會用到兩個嵌套的游標(biāo)來完成實現(xiàn)

3.第二步，查詢相應(yīng)的數(shù)據(jù)庫文檔，找到相應(yīng)能快速查詢到所有表，所有列的系統(tǒng)表

4.第三步，根據(jù)實現(xiàn)的思路，一步步的去實現(xiàn)完成

* 最終導(dǎo)出生成對應(yīng)的報表，報表能看到，當(dāng)前數(shù)據(jù)庫中有哪些表，哪個字段的值包含了當(dāng)前特殊字段

* 課外資料(必須要知道的那些潛規(guī)則)

1. sql 常見的系統(tǒng)表，以及系統(tǒng)表對應(yīng)的功能查詢

select * from sys.all_objects where type='u' ?---查詢當(dāng)前數(shù)據(jù)庫中所有的表，其中是屬于用戶創(chuàng)建的表，不是系統(tǒng)表

select * from ?information_schema.columns ? ? ---查詢當(dāng)前表中所有列，以及列的屬性(比如列的名稱，列的數(shù)據(jù)類型，后面有用到)

2. 游標(biāo)的工作執(zhí)行原理，以及相關(guān)的特性

3.存儲過程，以及sql字符串的拼接操作

4.調(diào)試使用好 print 關(guān)鍵字，打印出來相應(yīng)的sql語句來驗證自己的思路和結(jié)果是否正確

*注意事項

1.自己所寫的程序必須要嚴(yán)格優(yōu)化你的SQL，我一開始沒有優(yōu)化，導(dǎo)致執(zhí)行我的這個sql的時候數(shù)據(jù)庫出現(xiàn)相應(yīng)的異常，讓我好蛋疼，于是我猜想

異常：由于這個系統(tǒng)有1589張表，循環(huán)表就循環(huán)1589次，然后再1589次當(dāng)中還得循環(huán)每個表的字段(平均每個表有35個字段)，

那算起來大概要循環(huán)五萬次，然后這五萬次循環(huán)的時候 還得去模糊拼接查詢一次數(shù)據(jù)庫，估計游標(biāo)受不了，或者內(nèi)存不足，具體原因待查

2.細(xì)節(jié)注意的地方： exec sp_executesql 其中這個地方要注意，執(zhí)行這個的時候 它拼接生成的sql的標(biāo)量聲明必須是 nvarchar,不能是varchar,

這一點比較惡心，我這里就是少寫了個n，導(dǎo)致一直報錯。

3.循環(huán)所有的表，所有的字段，邏輯是這樣的，但是實際業(yè)務(wù)沒必要去這樣做，這里就做好優(yōu)化的相關(guān)代碼

比如我這里優(yōu)化了兩個：

第一. 排除了數(shù)據(jù)庫中表的數(shù)據(jù)是0的，也就是說當(dāng)前表中沒有任何數(shù)據(jù)，沒有數(shù)據(jù)的我就沒有必要去循環(huán)當(dāng)前表的結(jié)構(gòu)和模糊查詢了

第二. 模糊查詢肯定是針對表中字段是 字符串類型的，如果不是字符串類型的(text,varchar,nvarchar等)，當(dāng)然沒必要去循環(huán)模糊查詢了

第三. 后期還需要優(yōu)化的代碼

******************************************************************/

declare @QueryKeyword nvarchar(2000)

set @QueryKeyword='關(guān)鍵字'

declare @tblname nvarchar(200) --表名稱

declare @tablenum int ---獲取執(zhí)行查詢表中是否有數(shù)據(jù) 返回的結(jié)果 只要大于1 代表 這個表有數(shù)據(jù)，就進(jìn)行列的循環(huán)讀取和模糊匹配

declare @tablestr nvarchar(4000)

declare tbl_cursor cursor for select name from sys.all_objects where type='u'

---定義表的游標(biāo)，并且指定游標(biāo)的循環(huán)范圍 當(dāng)前數(shù)據(jù)庫的所有表(所有用戶表)

---獲取當(dāng)前數(shù)據(jù)庫中的表

open tbl_cursor --打開游標(biāo)

fetch next from tbl_cursor ?INTO @tblname --游標(biāo)開始循環(huán)讀取，一條條讀取，讀取每一條的時候 將讀到的表名稱 賦值給標(biāo)量@tblname

--將當(dāng)前游標(biāo)讀取到的表名稱 保存到標(biāo)量中，給下面的循環(huán)使用 每一次循環(huán)得到表名

while @@FETCH_STATUS = 0 ---只要讀到數(shù)據(jù)，就循環(huán)執(zhí)行下面的操作

begin

----開始 排除沒有數(shù)據(jù)的表 (優(yōu)化)

set @tablestr='select @b=count(*) from '+@tblname

exec sp_executesql @tablestr,N'@b int output',@tablenum output ---執(zhí)行當(dāng)前的數(shù)據(jù)結(jié)果集，有數(shù)據(jù)的表，才能進(jìn)行表結(jié)構(gòu)中每個字段的循環(huán)

if(@tablenum>0) --得到有數(shù)據(jù)的表

begin

declare @str nvarchar(4000)

declare @colvaluesql nvarchar(4000)

set @str='' --開始拼接 表中的字段

declare @data_typevalue nvarchar(200)

declare @num int

declare @colname nvarchar(200) ---表中列的標(biāo)量

declare col_cursor cursor for ?select column_name,data_type from information_schema.columns where table_name= @tblname order by column_name

--定義列的游標(biāo)，循環(huán)的范圍是當(dāng)前表中的所有列,以及列相應(yīng)的數(shù)據(jù)類型

--如果不是字符串類型的 都忽略掉

open col_cursor ?--打開游標(biāo)

fetch next from col_cursor into @colname,@data_typevalue --將列游標(biāo)讀到的列 放到變量中

while @@FETCH_STATUS = 0

begin

---初步判斷 數(shù)據(jù)類型符合的有 nvarchar ntext 兩種數(shù)據(jù)類型的 符合

--當(dāng)只有數(shù)據(jù)類型符合規(guī)則的時候 才生成對應(yīng)模糊查詢的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 ---執(zhí)行模糊查詢 返回模糊查詢的結(jié)果

if(@num>0) ---如果模糊查詢的結(jié)果 有值 那么就說明該表這的這個字段中包括這個 關(guān)鍵字

begin

-------這里 可以進(jìn)行相應(yīng)的擴(kuò)展，比如生成批量的查詢 修改，刪除語句,并且執(zhí)行

-------擴(kuò)展區(qū)

-------擴(kuò)展區(qū)

print @colvaluesql ?---打印出 符合模糊查詢的表 生成相應(yīng)的sql

exec(@colvaluesql) ?----執(zhí)行 sql

end

end

fetch next from col_cursor into @colname,@data_typevalue

END

close col_cursor ? ?---關(guān)閉列的游標(biāo)

deallocate col_cursor ?--釋放列的游標(biāo)

end

------結(jié)束 排除沒有數(shù)據(jù)的表 (優(yōu)化)

fetch next from tbl_cursor into @tblname ?--讓表的游標(biāo)繼續(xù)讀下一條數(shù)據(jù)

End

close tbl_cursor ?--關(guān)閉游標(biāo)

deallocate tbl_cursor ? --釋放游標(biāo)資源

總結(jié)

以上是生活随笔為你收集整理的cad模糊查询符号_万能模糊查询SQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。