可視化查詢(sp_helptext)——快速查詢包含指定字符串的存儲過程（附源碼）
- 2022 年 7 月 19 日
- 筆記
- 10分鐘系列, sp_helptext, SQLServer, SqlServer系列, sysobjects, 存儲過程
前言
在開發中，隨着業務邏輯的調整，修改存儲過程是必不可免的。
那怎麼定位到需要修改的存儲過程呢？一個一個的點開查詢？存儲過程少的話還行，一旦存儲過程過多，這樣是很浪費時間的，一個不注意還會遺漏掉。
在SqlServer中，我們可以使用 [1]sp_helptext 進行快速查詢文本內容。
sp_helptext是顯示規則、默認值、未加密的存儲過程、用戶定義函數、觸發器或視圖的文本。
sp_helptext獲取存儲過程內容的語法：
sp_helptext '存儲過程名稱'
那麼我們怎麼獲取存儲過程呢？是不是突然想到了什麼？沒錯，就是查詢系統對象表[2]sys.sysobjects，如下語法：
SELECT * FROM sys.sysobjects WHERE xtype='P'
到這裡不熟悉的肯定有點懵了，xtype=’P’是怎麼來的，有什麼依據，憑什麼xtype=『P』就是存儲過程，不慌，下面說一下xtype有哪些值類型。
xtype對象類型（type 是為了與過去的版本相兼容而存在的，SQL Server7.0 之後用 xtype 就可以了）:
| 類型值 | 值意義 |
| AF | 聚合函數 (CLR) |
| C | CHECK 約束 |
| D | DEFAULT（約束或獨立） |
| F | FOREIGN KEY 約束 |
| PK | PRIMARY KEY 約束 |
| P | 存儲過程 |
| PC | 程序集 (CLR) 存儲過程 |
| FN | 標量函數 |
| FS | 程序集 (CLR) 標量函數 |
| FT | 程序集 (CLR) 表值函數 |
| R | 規則（舊式，獨立） |
| RF | 複製篩選過程 |
| SN | 同義詞 |
| SQ | 服務隊列 |
| TA | 程序集 (CLR) DML 觸發器 |
| TR | DML 觸發器 |
| IF | 內聯表值函數 |
| TF | 表值函數 |
| U | 表（用戶定義類型） |
| UQ | UNIQUE 約束 |
| V | 視圖 |
| X | 擴展存儲過程 |
| IT | 內部表 |
所以上面的xtype=’P’代表的就是查詢存儲過程。
那到這裡就很清晰了，三步完成：
- 查詢出來所有的存儲過程(SELECT name FROM sys.sysobjects WHERE xtype=’P’)；
- 循環存儲過程獲取文本(sp_helptext ‘存儲過程名稱’)；
- 匹配文本裏面是否存儲指定字符串。
我們把這個步驟翻譯為可視化操作，直接點點點就行。
之前我們聊過代碼可視化（生成實體），我們就接着這個寫進行實現。
當然，你重新新建也可以，就幾行代碼完成，很簡單的。
可視化生成實體的感興趣的也可以去看看，演示地址：//entity.xiongze.net/
手把手教你基於SqlSugar4編寫一個可視化代碼生成器（生成實體，以SqlServer為例，文末附源碼）
效果展示
在線演示地址
在線演示地址： //entity.xiongze.net/Home/About
源碼下載地址
鏈接：//pan.baidu.com/s/1j-oU4gzszqV5nYN64WfLiw?pwd=xion
提取碼：xion
代碼實現
創建一個ASP.NET Web應用，命名為GenerateEntity，或者創建你自己的項目，哪一種都可以。
然後我直接貼出代碼，大家直接複製就可以使用。
頁面代碼
需要注意，這是基於jQuery的ajax請求，如果沒有進入jQuery的需要引入一下。
<div style="margin-top:10px;font-family:'Microsoft YaHei';font-size:18px; "> <h3 style="color:red;">根據關鍵字查詢存儲過程/函數</h3> <h4>下面server是需要連接的數據庫的服務器名稱，uid是登錄名，pwd是密碼，database是指定數據庫名</h4> <div style="height:100px;width:100%;border:1px solid gray;padding:10px"> <div> <span>鏈接數據庫：</span> <input style="width:100%;max-width:800px;" id="Link" value="server=192.168.0.1;uid=sa;pwd=123456;database=mydatabase" /> </div> <div style="margin-top:10px"> <span>查詢關鍵字：</span> <input style="width:400px;max-width:400px;" placeholder="輸入需要查詢的關鍵字" autofocus id="keyWord" /> <span>生成類型：</span> <select id="type"> <option value="0">查詢存儲過程</option> <option value="1">查詢函數</option> </select> <a href="javascript:void(0)" onclick="GenerateEntity()" style="margin-left:20px;font-weight:600;">查詢關鍵字所在位置</a> </div> </div> <div style="height:720px;width:100%;float:left;border:1px solid gray;overflow: auto;padding:10px;" id="showTable"> </div> </div> <script type="text/javascript"> function GenerateEntity() { var keyWord = $("#keyWord").val().trim(); if (keyWord == "") { alert("需要查詢的關鍵字不能為空"); return; } $.ajax({ url: "/Home/GetKeyWord", data: { Link: $("#Link").val(), keyWord: keyWord, type: $("#type").val() }, type: "POST", async: false, dataType: "json", success: function (data) { if (data.res) { if (data.info != "") { var info = eval("(" + data.info + ")"); $("#showTable").html(""); var showTable = '<h4>總共查詢出 <span style="color:red">' + info.length + '</span> 條數據</h4><br />'; for (var i = 0; i < info.length; i++) { showTable += "<a>" + info[i] + "</a><br/>"; } $("#showTable").html(showTable); } } else { alert(data.msg); $("#showTable").html("沒有查詢到數據！"); } } }); } </script>
後端代碼
#region 根據關鍵字查詢存儲過程/函數 //根據數據庫名查詢所有表 public JsonResult GetKeyWord(string Link, string keyWord,string type) { ResultInfo result = new ResultInfo(); int num = 0; List<string> TextList = new List<string>(); try { string sql = @"select 'sp_helptext ' + name from sys.sysobjects where xtype = 'P'"; //查詢所有的存儲過程 if (type == "1") sql = @"select 'sp_helptext ' + name from sys.sysobjects where xtype in ('FN', 'TF') order by xtype, name"; //查詢所有函數 DataTable dt = GetDataToDt(sql, "dtTable", Link); //連接數據庫查詢【存儲過程】數據 if (dt != null && dt.Rows.Count > 0) { //循環存儲過程 foreach (DataRow dr in dt.Rows) { DataTable dt_text = GetDataToDt(dr[0].ToString(), "dtTable_text", Link); //連接數據庫查詢【存儲過程】文本內容 if (dt_text != null && dt_text.Rows.Count > 0) { foreach (DataRow rr in dt_text.Rows) { if (rr[0].ToString().ToLower().IndexOf(keyWord.ToLower()) != -1) //判斷是匹配指定字符串 { TextList.Add(dr[0].ToString().Replace("sp_helptext ", "")); //將存儲過程名稱添加到返回的集合列表 num++; break; } } } } } result.info = Newtonsoft.Json.JsonConvert.SerializeObject(TextList); result.res = true; result.msg = "查詢成功！"; } catch (Exception ex) { result.msg = ex.Message; } return Json(result, JsonRequestBehavior.AllowGet); } /// <summary> /// ORM數據庫連接 /// </summary> /// <param name="sql">查詢語句</param> /// <param name="tbName">自定義的表名</param> /// <param name="conStr">數據庫連接</param> /// <returns></returns> public static DataTable GetDataToDt(string sql, string tbName, string conStr = null) { //這裡使用using或者手動Close都可以 SqlConnection sqlCon = new SqlConnection(conStr); DataSet ds = new DataSet(); DataTable dt = null; try { SqlCommand cmd = new SqlCommand(sql, sqlCon); cmd.CommandTimeout = 1000; SqlDataAdapter sqlDa = new SqlDataAdapter(cmd); sqlCon.Open(); sqlDa.Fill(ds, tbName); if (ds != null && ds.Tables.Count > 0) { dt = ds.Tables[tbName]; } } catch (Exception ex) { dt = null; } finally { sqlCon.Close(); } return dt; } #endregion //封裝返回信息數據 public class ResultInfo { public ResultInfo() { res = false; startcode = 449; info = ""; } public bool res { get; set; } //返回狀態（true or false） public string msg { get; set; } //返回信息 public int startcode { get; set; } //返回http的狀態碼 public string info { get; set; } //返回的結果（res為true時返回結果集，res為false時返回錯誤提示） }
這樣一套可視化【快速查詢包含指定字符串的存儲過程】就出來了，我們把他發佈到IIS上面，然後設置為瀏覽器標籤（收藏），這樣就可以快捷使用了。
我們運行一下看看，是不是感覺很方便呀！
PS：數據庫連接地址一定要填正確，否則數據返回結果為空。
總結
存儲過程及裏面的文本包含的指定字符串就是這麼查詢。
那其餘的我們是不是可以舉一反三了？例如函數、視圖、表都可以按照這種方式來，只需要把xtype的條件替換一下即可。
感興趣的話趕快去試試吧。
參考文獻
喜歡就點贊加關注。
歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
QQ群：711838388
出處：//www.cnblogs.com/xiongze520/p/16491802.html
您可以隨意轉載、摘錄，但請在文章內註明作者和原文鏈接。
