技術文章 » 視覺化查詢(sp_helptext)——快速查詢包含指定字串的儲存過程（附原始碼）

視覺化查詢(sp_helptext)——快速查詢包含指定字串的儲存過程（附原始碼）

2022-07-19 15:01:12

前言

在開發中，隨著業務邏輯的調整，修改儲存過程是必不可免的。

那怎麼定位到需要修改的儲存過程呢？一個一個的點開查詢？儲存過程少的話還行，一旦儲存過程過多，這樣是很浪費時間的，一個不注意還會遺漏掉。

在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'代表的就是查詢儲存過程。

那到這裡就很清晰了，三步完成：

  1. 查詢出來所有的儲存過程(SELECT name  FROM sys.sysobjects  WHERE xtype='P')；
  2. 迴圈儲存過程獲取文字(sp_helptext '儲存過程名稱')；
  3. 匹配文字裡面是否儲存指定字串。

我們把這個步驟翻譯為視覺化操作，直接點點點就行。

之前我們聊過程式碼視覺化（生成實體），我們就接著這個寫進行實現。

當然，你重新新建也可以，就幾行程式碼完成，很簡單的。

視覺化生成實體的感興趣的也可以去看看，演示地址：http://entity.xiongze.net/

手把手教你基於SqlSugar4編寫一個視覺化程式碼生成器（生成實體，以SqlServer為例，文末附原始碼）

效果展示

線上演示地址

線上演示地址： http://entity.xiongze.net/Home/About

原始碼下載地址

連結：https://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的條件替換一下即可。

感興趣的話趕快去試試吧。

參考文獻

[1].sp_helptext - 百度百科

[2].sysobjects - 百度百科

 

喜歡就點贊加關注。

歡迎關注訂閱微信公眾號【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
QQ群：711838388
出處：https://www.cnblogs.com/xiongze520/p/16491802.html
您可以隨意轉載、摘錄，但請在文章內註明作者和原文連結。