[翻译]SQL Server 未公开的两个存储过程sp_MSforeachtable 和 sp_MSforeachdb

阅读量：5128 次

发布时间：2019-06-13

本文共 3200 字，大约阅读时间需要 10 分钟。

SQL Server 未公开的两个存储过程sp_MSforeachtable 和 sp_MSforeachdb

您是否曾经写过代码来处理数据库中的所有表？处理一个 SQL Server实例中的所有数据库的代码又该如何写？然则，您是否知道有多种方法可以解决这问题？您可以创建一个游标cursor包含所有数据表，或者包含SQL Server实例的所有数据库；或者使用非公开（undocumented）的存储过程。本文将向您阐述非公开的存储过程的工作方式，以及应用实例向您展示如何使用它们。非公开的存储过程比之游标更易用。

概述

Microsoft 提供了两个非公开化的存储过程，让您可以迭代处理数据库中的所有表，或者SQL Server 实例中的所有数据库。第一个存储过程是"sp_MSforeachtable"，让您可以轻易地使用代码处理数据库中的所有表；另一个是"sp_MSforeachdb"，处理SQL Server 实例中的所有数据库。让我们深入地了解这两个存储过程。

sp_MSforeachtable

"sp_MSforeachtable"没有在在线文档中出现，它存在于master数据库中，可以对给定数据库的所有表执行单条或多条T-SQL命令，请看下面的例子。

假如，您需要创建一个临时表，记录当前数据库拥有的表的表名、行记录数。为了实现此功能，您需要执行这样的命令："select '<mytable>', count(*) from <mytable>"。其中"<mytable>"替换为数据库中的每个表名，并将结果插入到临时表。下面我们用游标与非公开的"sp_MSforeachtable"来分别实现。

使用游标的方式：

下面是输出结果:

下面代码应用非公开的"sp_MSforeachtable"生成相同的结果：

下面是结果:

可见，使用游标与sp_MSforeachtable可生成相同的结果，您认为哪种方式更具可读性，更简单？下面来详细介绍sp_MSforeachtable的语法：

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,

@command3, @whereand, @precommand, @postcommand

说明:

@RETURN_VALUE – 返回值

@command1 – 类型是nvarchar(2000)，sp_MSforeachtable最先执行的命令

@replacechar – 处理过程中，将命令行的这个字符替换为具体的表名（默认是"?"）

@command2\@command3:对每个数据表，都会执行这两条命令，@command2在@command1之后执行，@command3在@command2之后执行

@whereand – 类型是varchar(2000),提供额外的约束来过滤 sysobjects 表的行

@precommand - 类型是varchar(2000)，在处理任何表之前执行此命令

@postcommand - 类型是varchar(2000)，在处理完所有表之后执行此命令

下面几个例子演示此存储过程的用法，处理所有表或者部分表。

下面查询以字母 p 开头的表，使用参数 @whereand 设置过滤条件，代码如下：

下面是结果:

上面的代码使用了参数 @command1 与 @whereand，参数 @whereand 用来设置 WHERE 条件，筛选出以字母 p 开头的表名，我设置了参数值为"and o.name like ''p%''"。如果您希望使用多个条件约束，如以 p 开头或者以 a 开头，设置参数值为：

and o.name like ''p%'' or o.name like ''a%''

如果语句有问题，将 name 的前缀去掉，如下：

and name like ''p%'' or name like ''a%''

注意，上面例子的参数 @command1 使用了"?"，它叫做替换字符（replacement character），默认被所有表名替换。如果您需要在命令中使用"?"作为内容而不是被表名替换的替换字符，那么可以使用参数 @replacechar 来设置替换字符。下面例子使用"{"作为替换字符。

下面是结果:

还有两个参数 @precommand 与 @postcommand，看下面例子，把上面例子中的所有语句整合为一个简洁的存储过程调用。

注意上面例子用了全局临时表 ##rowcount，如果用临时表 #rowcount会报错。参数 @precommand 创建全局临时表，只执行了一次，并先于 @command1 的语句执行。@postcommmand 的语句待迭代处理完所有表后执行，也仅执行一次，用于显示结果并删除临时表。

sp_MSforeachdb

sp_MSforeachdb 同样也是在 master 数据库中，它迭代SQL Server 实例中的每个数据库，以执行T-SQL 语句，如"DBCCCHECKDB"，在看看它的语法

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,

@command2, @command3, @precommand, @postcommand

说明:

@RETURN_VALUE – 返回值

@command1 – 类型是 nvarchar(2000),最先执行的命令

@replacechar – 替换字符，命令字符串中被替换为实际的数据库名（默认是"?"）

@command2\@command3:对每个数据库，都会执行这两条命令，@command2在@command1之后执行，@command3在@command2之后执行

@precommand - 类型是varchar(2000)，在处理任何数据库之前执行此命令

@postcommand - 类型是varchar(2000)，在处理完所有数据库之后执行此命令

sp_MSforeachdb 的参数与sp_MSforeachtable 的参数类似，因此，不再特意介绍这些参数。

请看下面的简单例子，此例子将进行数据库备份，然后对每个数据库做"DBCC CHECKDB"：

这里我用了三条不同的命令，第一条打印正在处理的数据库名。sp_MSforeachtable 有一个参数用来过滤需要处理的数据表，但是sp_MSforeachdb没有类似的过滤参数。由于SQL Server 不支持对 tempdb 的备份，因此我要跳过tempdb，这是我在每条命令使用 IF 的原因。第二条命令进行数据库备份，最后一条命令对除 tempdb 之外的数据库运行"DBCC CHECKDB"。

运行上面命令之前要先创建目录"c:\temp",下面是部分输出结果：