SQL Server系统存储过程和参数总结

一些用在SQL 2000的企业管理GUI中，并且不打算用于其他的流程。微软已预计将其中的一些存储过程从未来的SQL Server版本中删除(或已经删除了)。虽然这些存储过程可能很有用并为你节省了很多时间，但是他们可以在任何时候改变他们的函数或简单的删除掉。

　　下面的图表显示了当许多存储过程从一个Microsoft SQL Server版本移入另一个版本时，引入了新的存储过程，而原来的一些则从安装包里删除了。大多数的存储过程，如果不是所有的，要求用户是系统管理员服务器角色以便执行这些存储过程。和文件系统交互的存储过程还要求执行存储过程的用户(还有SQL Server的服务帐户)具有访问文件/文件夹的权限。

存储过程名称	SQL 2000	SQL 2005	SQL 2008
sp_executeresultset	X
sp_MSforeachdb	X	X	X
sp_MSforeachtable	X	X	X
sp_readerrorlog	X	X	X
xp_create_subdir		X	X
Xp_delete_file		X	X
xp_dirtree	X	X	X
xp_fileexist	X	X	X
xp_fixeddrives	X	X	X
xp_getfiledetails	X
xp_getnetname	X	X	X
xp_loginconfig	X	X	X
xp_makecab	X
xp_msver	X	X	X
xp_get_mapi_profiles	X	X	X
xp_subdirs	X	X	X
xp_test_mapi_profile	X	X	X
xp_unpackcab	X

　　sp_executeresultset

　　微软在SQL Server 2005中删除了这个名为sp_executeresultset的便利小程序。它允许你在空闲时通过使用SELECT查询产生动态SQL代码。然后，作为结果的SQL命令将会在数据库上执行。它允许你创建单独的一行代码，这行代码可以在单步中查询到你的数据库里的每一个表的记录数目(就像例子中所显示的)。这是一个未公开的存储过程，而且无法知道它为什么被删除了。但是，唉，这个便利的有用存储过程已经没有了。　　

exec sp_execresultset 'SELECT ''SELECT '''''' + name + '''''',

　　count(*) FROM '' + name

　　from sysobjects

　　where xtype = ''U'''

　　sp_MSforeachdb / sp_MSforeachtable

　 sp_MSforeachdb / sp_MSforeachtable 　　

　　两个存储过程，sp_MSforeachdb 和sp_MSforeachtable封装了一个指针。它们允许你对你的SQL Server上的每一个数据库和当前数据库中的每一个表分别执行T-SQL代码。然而你不能在SQL2000和之前的版本中在一个 sp_MSforeachdb命令中使用sp_MSforeachtable命令。在这些存储过程中使用的指针名称是一样的(hCForEach)，因此在每一次执行sp_MSforeachtable时会返回一个错误说该指针名称已经在使用。在SQL Server 2005中，微软解决了这一问题。为了执行“下一个”命令，你必须告诉其中的一个存储过程它将使用一个不同的替换字符而不是默认的问号。我改变了数据库命令中的这个替换字符，因为它更为简单。

　　打印当前数据库中的每一个表的名称

< p>　　exec sp_MSforeachtable 'print ''?'''

　　打印当前服务器的每一个数据库

　　exec sp_MSforeachdb 'print ''?'''

　　打印当前数据库的每一个表

　　exec sp_MSforeachdb 'use [@] exec sp_MSforeachtable ''print

　　''''@.?''''''', '@'

　　sp_readerrorlog / xp_readerrorlog

　　存储过程sp_readerrorlog实际上有两种形式。每一项的工作内容一样;一个是另一个的简单封装。封装的存储过程是sp_readerrorlog，它调用 xp_readerrorlog。这两个都有四个输入参数，但是只有前两个对我们有用。第一个参数设定你希望看到的文件编号。第二个是要查看的日志(对于 ERRORLOG是1或空，对SQL Agent Log是2)。这允许你快速并很容易地查看你的错误日志，而不是得查看SQL Server 2005和SQL 2008所带来的臃肿的日志阅览器。

　　查看当前的SQL ERRORLOG文件。

　　exec sp_readerrorlog

　　exec sp_readerrorlog 0, 1

　　查看之前的SQL Agent Log文件。

　　exec sp_readerrorlog 1, 2

　　xp_create_subdir

　　在SQL Server 2005中引入的xp_create_subdir存储过程是非常轻便的，因为你可以用它在SQL Server的硬盘上或从T-SQL内部的网络共享上创建文件夹。

　　exec xp_create_subdir 'c:MSSQLData'

　　xp_delete_file

　　使用SQL Server 2005中引入的xp_delete_file存储过程从SQL Server的硬盘或从T-SQL内部的网络共享上删除文件。

　　 xp_dirtree

　　xp_dirtree存储过程允许你查看文件夹树状结构和/或一个文件夹下的文件列表。这个存储过程有几个参数用来控制这个存储过程查询深度和是返回文件和文件夹还是只返回文件夹。第一个参数设定要查看的文件夹。(建议;不要在Windows的系统盘根目录上执行这个存储过程，因为产生树和返回数据需要一些时间。)第二个参数限制了这个存储过程将会进行的递归级数。默认是零或所有级别。第三个参数告诉存储过程包括文件。默认是零或只对文件夹，数值1代表包括结果集的文件。定义第三个参数为不为零的数值将会增加一行到输出的调用文件，这个文件是显示进入一个文件夹或文件的一个小文件。

　　获得完整目录树。

　　exec xp_dirtree 'd:mssql'

　　获得目录树的前两级。

　　exec xp_dirtree 'd:mssql', 2

　　获得目录数的前三级，包括文件。

　　exec xp_dirtree 'd:mssql', 3, 1

　　exec xp_dirtree 'd:mssql'

　　xp_fileexist

　　这个SQL Server存储过程，xp_fileexist，是用来决定一个文件是存在于的硬盘上还是在网络共享上。它对于从规则文档中下载数据的存储过程是非常有用的。它允许你在打算盲目的下载数据之前查看文件是否存在。这个存储过程有两个参数。用第一个参数来确定你想要的文件或文件夹是否存在。第二个参数是一个输出参数，如果它被定义了，会根据文件存在或不存在而返回1或0。

　　没有这个参数：

　　exec xp_fileexist 'c:importfile.csv'

　　有这个参数：

　　 DECLARE @file_exists int

　　exec xp_fileexist 'c:importfile.csv', @file_exists OUTPUT

　　SELECT @file_exists

　　xp_fixeddrives

　　xp_fixeddrives存储过程是最有用的存储过程之一。它展示了一个列表所有驱动器名和每个驱动器上的空闲空间大小。这个参数有一个单独的可选输入参数，它可以按驱动器类型过滤结果。设置为数值3将会返回所有的大量存储设备(CD -ROM，DVD等等);设置为数值4将会返回硬盘驱动器;而当设置为数值2时将会返回可移动的设备(USB插拔驱动器，闪存驱动器等等)。

　　返回所有的驱动器。

　　exec xp_fixeddrives

　　只返回硬盘驱动器

　　exec xp_fixeddrives 2

　　xp_getfiledetails

　　xp_getfiledetails是另一个非常有用的存储过程，在SQL Server 2000版本中最后一次可用。这个存储过程返回关于指定文件的大小、日期和属性信息，包括创建、访问和修改的日期和次数。

< p>　　exec xp_getfiledetails 'c:filetoload.csv'

　　xp_getnetname

　　xp_getnetname存储过程返回Microsoft SQL Server安装所在的物理机器的名称。你可以使机器名称作为数据集返回或作为变量返回。

　　没有这个参数

　　exec xp_getnetname

　　使用这个参数

　　DECLARE @machinename sysname

　　exec xp_getnetname @machinename OUTPUT

　　select @machinename

　　xp_loginconfig

　　SQL Server存储过程将会告诉你关于执行它的用户的一些基础的校验信息。它告诉你校验方法(Windows或SQL登录)、服务器的默认域、审计级别，还有一些内部分隔符信息。

　　exec xp_loginconfig

　　xp_makecab

　　在SQL Server 2000中，微软给了我们直接从T-SQL压缩系统文件的能力，不必再到DOS下通过xp_cmdshell和运行第三方软件，像pkzip或 winzip来完成。这个命令就是xp_makecab。它允许你指定一列你想压缩的文件还有你想放进去的cab文件。它甚至允许你选择默认压缩， MSZIP压缩(类似于.zip文件格式)或不压缩。第一个参数给出到cab文件的路径，这是你想创建和添加文件的地方。第二个参数是压缩级别。如果你想使用详细的日志记录就使用第三个参数。第四个参数后跟着你想压缩的文件的名称。在我的测试里，我可以在扩展存储过程里传45个要压缩的文件名称，这意味着它是一个对你的数据压缩要求来说非常灵活的解决方案。

　　exec xp_makecab 'c:test.cab', 'mszip', 1, 'c:test.txt' , 'c:test1.txt'

　　xp_msver

　　存储过程xp_msver在查看系统信息的时候是非常有用的。它返回关于主机操作系统的大量的信息——SQL 版本号、语言、CPU类型、版权和商标信息、Microsoft Windows版本、CPU数和亲和性设置、物理内存设置和你的产品键。这个存储过程有许多输入参数，它们允许你过滤返回的记录。每一个参数都是一个 sysname数据类型，它接受一条记录的名称。如果指定了所有的参数，那么只有指定的行作为参数返回。