实用技巧：创建你自己的系统存储过程

核心提示：通过在一个SQL Server主数据库中创建一个带有sp_前缀的存储过程，你可以充分利用引擎存储过程名称解析算法的优势，实用技巧：创建你自己的系统存储过程，如果一个存储过程以sp_开头，那么这个引擎将会在检查当前数据库和属主之前先查找主数据库，应该注意的是，把对象增加到该主数据库中一般不会被当作一种好的行为，顺便说一句

通过在一个SQL Server主数据库中创建一个带有sp_前缀的存储过程，你可以充分利用引擎存储过程名称解析算法的优势。如果一个存储过程以sp_开头，那么这个引擎将会在检查当前数据库和属主之前先查找主数据库。顺便说一句，这个名称解析正好是你从来不把自己应用数据库存储过程的前缀命名为sp_，因为每次你尝试着执行这些程序时，你都会不断地产生与不必要查询和缓存缺失有关的费用。

当从主数据库中运行一个带着sp_前缀的程序时，有一些细微的地方需要注意，比如用哪一个数据库上下文环境，什么影响了数据所返回的。

请看下面的例子:

　　　USE MASTER
　　GO
　　IF OBJECT_ID('SP_GETOBJECTS') IS NOT NULL
　　DROP PROCEDURE DBO.sp_GETOBJECTS
　　GO
　　CREATE PROCEDURE DBO.sp_GETOBJECTS
　　AS
　　SET NOCOUNT ON
　　SELECT DB_NAME()
　　-- ANSI view
　　SELECT *
　　FROM INFORMATION_SCHEMA.TABLES
　　WHERE TABLE_NAME = 'Employee'
　　-- SQL Server 2005 table
　　SELECT *
　　FROM sys.objects
　　WHERE NAME = 'Employee'
　　-- SQL Server 2000 table
　　SELECT *
　　FROM sysobjects
　　WHERE NAME = 'Employee'
　　SELECT TOP 1 *
　　FROM HumanResources.Employee
　　GO
　　USE AdventureWorks
　　GO
　　EXEC sp_GETOBJECTS
　　GO

通过查看输出结果，我们可以看到，正如执行SQL Server 2000语句一样，DB_NAME()在AdventureWork数据库上下文环境下运行，以此从系统对象中找回员工表的元数据。然而，相同意义的INFORMATION_SCHEMA 和SQL Server 2005语句不能利用这个方法，并且员工表的查询彻底失败。这种方法行不通，是因为实际上这两个语句在主数据库上下文环境下执行。当在主数据库中找到这个存储过程时，把当前数据库上下文环境应用到SQL Server 2005具体目录信息是不明智的。

图1

图2

那么，SQL Server 2000系统表最终淘汰，我们要如何解决这个问题呢?这里有一个无正式文件说明的系统存储过程叫做sp_ms_marksystemobject，它可以用来标记该引擎，因此这个存储过程应该可以运行，尽管它是一个微软提供的系统存储过程。与其它无正式文件说明的命令一样，它有可能在以后的一个版本中消失，所以你可能要自担风险来使用它。

　　　USE MASTER
　　GO
　　EXEC sp_ms_marksystemobject 'SP_GETOBJECTS'
　　GO
　　SELECT NAME, IS_MS_SHIPPED
　　FROM SYS.OBJECTS
　　WHERE NAME = 'SP_GETOBJECTS'
　　GO

把我们的存储过程记为sp_ms_marksystemobject表示它已经用引擎登录了，因为它是一个微软提供的过程。

图3

现在重新运行这个存储过程会显示所有的句子是在AdventureWorks数据库环境下运行的。

　　　USE AdventureWorks
　　GO
　　EXEC sp_GETOBJECTS
　　GO

图4

正如你所看到的，这是一种有效方法，这样你才可以专注于你经常运行的查询，因而这些查询在服务器的多个数据库中也是可用的。应该注意的是，把对象增加到该主数据库中一般不会被当作一种好的行为，最好不要在生产数据库中使用这个功能。