打造自己的辅助工具,提高开发效率
本文涉及的知识是非常简单而又基础的,但是活用这些知识可以显著的提高您的开发效率,您还可以发挥创意扩展自己的开发工具。
1、基础知识点:
知识点1:可以在当前数据库直接访问master数据库中存储过程名称以sp_开始(且必须是以sp_开始)的存储过程。
说明:执行存储过程时,“如果过程名称的前三个字符为 sp_,SQL Server 会在 Master 数据库中寻找该过程。如果没能找到合法的过程名称,SQL Server 会寻找所有者名称为 dbo 的过程”(参见Sql Server联机帮助文档(索引“存储过程:执行”))。这实际上是在暗示我们用户自定义的存储过程名称最好不要用sp_开始,这样可以缩短(可能没有那么显著)存储过程时间。
知识点2:在开发过程中,常常要察看一个表中的数据,经常在查询分析器中重复的输入“select * from ”您是不是觉得很烦,至少我觉得很烦。其实我们可以将常用的语句在查询分析器中定义为一个快捷键(如图一),要执行时仅需按快捷就可以直接执行,省掉输入过程,少了输入的错误,是不是很爽?运用知识点1中的技巧,将常用语句写成一个存储过程(为什么要写成存储过程在后面详述)放master数据库中,就可以在任何数据库下调用您的常用语句了。
说明:当您按下快捷键执行命令时,查询分析器首先调用“sp_sproc_columns”存储过程,判断此快捷键对应的语句输入参数是什么,若sp_sproc_columns返回空结果集(非存储过程也会返回空结果集),则认为执行此语句不需要参数,直接执行此语句;若有结果集,则将查询分析器中选中部分作为参数传入并执行语句。所以,我们简单的要将“select * from”这类语句定义为快捷键命令都必须要定义为存储过程,否则无法接收传入参数。
图一、将常用语句定义为快捷键
2、看我抛砖:
结合实际工作,我写了四个常用存储过程,定义为快捷键,实现即时快速调用。
2.1、表结构显示
过程名:sp_tableframe
缘起:开发中我们严禁在数据窗口、视图、存储过程中存在“select *”和“insert into 表 values”这类不指明目标列的写法,但要逐个字段输入太烦易错;存储过程中常常存在从物理表形成临时表的需求,严谨的写法是“create table #临时表名”(select * into #临时表名 的写法可能会锁tempdb数据库,不建议使用),但create table 后的内容要逐个字段输入也是太烦易错;我们还常有要看表字段的中文注释、数据类型、默认值等内容的需求。
代码:(略)
结果集:(共有4个)
结果集一为表的概况;结果集二为表的列名、类型、主键、描述等属性;结果集三把表的列名按逗号分隔成字符串,使用时直接复制粘贴即可避免重复输入;结果集四和三类似,但带上了字段类型定义信息,可以复制出来用于创建临时表。
2.2、锁表进程查询
过程名:sp_ShowLock
缘起:当出现锁表时,我们急需要知道的是谁锁了谁,是在哪个语句上锁了,冲突的资源是什么。但Sql Server缺乏一个能满足我们要求的命令,得,自己写一个。
代码:(略)
结果集:(共有2个)
结果集一:被阻进程、被阻语句、阻进程、阻塞语句、等待时间、CPU时间、磁盘累计读写、登录时间、打开事务、状态、网卡地址
结果集二:锁定发生的数据库、锁定对象、类型、模式、状态
2.3、表空间使用统计查询
过程名:sp_tablespaceused
缘起:在上几十G的数据库中,我们可能关心数据库中哪个表最大,要知道每个表在数据库中空间占用的大致情况,此过程为解决此问题而做。
代码:(略)
结果集:(略)
2.4、形成数据字典
过程名:sp_MsDataDict
缘起:整理改善了原来的导出表结构的语句。
结果集:(略)
以上主要介绍了两个知识点,围绕这两个知识点讲了四个例子,希望能起到抛砖引玉的作用,对大家有所帮助。