<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时返回错误提示）
        }