西安专业网站建设,体育新闻,wordpress自动链接到图片大小,一级造价师考试科目SQL Server 未公开的两个存储过程sp_MSforeachtable 和 sp_MSforeachdb 您是否曾经写过代码来处理数据库中的所有表#xff1f;处理一个 SQL Server实例中的所有数据库的代码又该如何写#xff1f;然则#xff0c;您是否知道有多种方法可以解决这问题#xff1f;您可以创建…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_VALUEsp_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,下面是部分输出结果 使用SQL Server非公开存储过程的说明 当使用这些非公开的存储过程时您须小心并进行测试。由于未公开意味着Microsoft在任何版本的升级或者补丁包都可能对它们进行修改并且不做任何告知。因此您需要在所有的SQL Server版本做全面的测试测试以验证您的代码是否在新版本中仍然正常运行。 结语 正如您所见这两个非公开的存储过程比游标易用以后您可以用它们来迭代处理数据表或数据库。但是请谨记这两个存储过程是非公开的Microsoft很可能会随时改变它们的功能。 参考 SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb sp_MSforeachtable 转载于:https://www.cnblogs.com/feixian49/archive/2011/05/10/2042733.html
