在开发中,随着业务逻辑的调整,修改存储过程是必不可免的。
那怎么定位到需要修改的存储过程呢?一个一个的点开查询?存储过程少的话还行,一旦存储过程过多,这样是很浪费时间的,一个不注意还会遗漏掉。
在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'代表的就是查询存储过程。
那到这里就很清晰了,三步完成:
我们把这个步骤翻译为可视化操作,直接点点点就行。
之前我们聊过代码可视化(生成实体),我们就接着这个写进行实现。
当然,你重新新建也可以,就几行代码完成,很简单的。
手把手教你基于SqlSugar4编写一个可视化代码生成器(生成实体,以SqlServer为例,文末附源码)
链接: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的条件替换一下即可。
感兴趣的话赶快去试试吧。