浅析sp_MSforeachtable和sp_MSforeachdb的用法

1. 概述

系统存储过程sp_MSforeachtable 和sp_MSforeachdb, 是微软提供的两个不公开的存储过程, 从ms sql 6.5 开始。

存放在SQL Server 的MASTER 数据库中。

我们在master 数据库里执行下面的语句可以看到两个proc 详细的代码

 

use master

 

exec sp_helptext sp_MSforeachtable

 

exec sp_helptext sp_Msforeachdb

 

2.sp_MSforeachtable

 

2.1 sp_MSforeachtable 参数说明:

/*********************************************************************************

sp_MSforeachtable 系统存储过程有6 个参数:

@command1 nvarchar(2000),          -- 第一条运行的SQL 指令

@replacechar nchar(1) = N'?',      -- 指定的占位符号

@command2 nvarchar(2000)= null,    -- 第二条运行的SQL 指令

@command3 nvarchar(2000)= null,    -- 第三条运行的SQL 指令

@whereand nvarchar(2000)= null,    -- 可选条件来选择表

@precommand nvarchar(2000)= null,  -- 执行指令前的操作( 类似控件的触发前的操作)

@postcommand nvarchar(2000)= null  -- 执行指令后的操作( 类似控件的触发后的操作)

/*********************************************************************************

 

2.2 sp_MSforeachtable 使用举例

 

-- 统计数据库里每个表的详细情况

exec sp_MSforeachtable @command1="sp_spaceused '?'"

--把上面的结果存入表中 方便查看

create table test(name varchar(100), rows int,reserved varchar(100),data varchar(20),index_size varchar(20),unused varchar(20))

insert into test
EXEC   sp_MSforeachtable   " exec sp_spaceused   '?'"

-- 获得每个表的记录数和容量:

EXEC sp_MSforeachtable @command1="print '?'",

@command2="sp_spaceused '?'",

@command3= "SELECT count(*) FROM ? "

 

 

-- 更新PUBS 数据库中已t 开头的所有表的统计:

EXEC sp_MSforeachtable @whereand="and name like 't%'",

@replacechar='*',

@precommand="print 'Updating Statistics.....' print ''",

@command1="print '*' update statistics * ",

@postcommand= "print''print 'Complete Update Statistics!'"

 

-- 删除当前数据库所有表中的数据

sp_MSforeachtable @command1='Delete from ?'

sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

 

参数@whereand 的用法

@whereand 参数在存储过程中起到指令条件限制的作用, 具体的写法如下:

@whereend, 可以这么写 @whereand=' AND o.name in (''Table1'',''Table2'',.......)'

例如: 我想更新Table1/Table2/Table3 中NOTE 列为NULL 的值

sp_MSforeachtable @command1='Update ? Set NOTE='''' Where NOTE is NULL',@whereand=' AND o.name in

(''Table1'',''Table2'',''Table3'')'

 

 

3.sp_msforeachdb

 

3.1 sp_msforeachdb 参数说明:

/*********************************************************************************

　sp_MSforeachdb 系统存储过程有6 个参数:

    @command1 nvarchar(2000) -- 第一条运行的t-sql 指令

    @replacechar nchar(1) = N'?' -- 指定的占位符号

    @command2 nvarchar(2000) = null -- 第二条运行的t-sql 指令

    @command3 nvarchar(2000) = null  -- 第三条运行的t-sql 指令

    @precommand nvarchar(2000) = null  -- 在sp_MSforeach_worker 前执行的指令

    @postcommand nvarchar(2000) = null -- 在sp_MSforeach_worker 后执行的指令

************************************************************************************/

 

3.2 sp_msforeachdb 用法举例

sp_msforeachdb 除了@whereand 外, 和sp_msforeachtable 的参数是一样的, 我们可以通过这个存储过程检测所有的数据库

 

-- 获得所有的数据库的存储空间:

exec sp_msforeachdb @command1="print '?'",

@command2="sp_spaceused "

 

-- 检查所有的数据库

exec sp_msforeachdb @command1="print '?'",

@command2="dbcc checkdb (?) "

 

4. "?" 说明

"?" 在存储过程的特殊用法, 造就了这两个功能强大的存储过程

这里"?" 的作用, 相当于DOS 命令中、以及我们在WINDOWS 下搜索文件时的通配符的作用。