parameters
type
sp_msforeachtable
sp_msforeachdb
description
@precommand
nvarchar(2000)
yes
yes
this command is executed before any commands and can be used for setting up an environment for commands execution.
@command1
nvarchar(2000)
yes
yes
first command to be executed against each table/database.
@command2
nvarchar(2000)
yes
yes
second command to be executed against each table/database.
@command3
nvarchar(2000)
yes
yes
third command to be executed against each table/database.
@postcommand
nvarchar(2000)
yes
yes
this command is executed after any other commands and can be used for cleanup process after commands execution.
@replacechar
nchar(1)
yes
yes
default value is “?” which represents the database/table name. you may need to change this value if you want “?” mark to be used in your query.
@whereand
nvarchar(2000)
yes
no
with this you can specify the filtering criteria for your table collection. for details see the script div,
脚本1演示了sp_msforeachtable的用法。第1条语句列出当前库所有的表和总的记录数，而语句2输出当前库下各表的空间占用情况。（注：在@cmd里用’’表示单引号，如select ‘’?’’）
script #1 : sp_msforeachtable system stored procedure
–list all the tables of current database and total no rows in it
exec sp_msforeachtable ‘select ”?” as tablename, count(1)
as totalrows from ? with(nolock)’
–list all the tables of current database and space used by it execute sp_msforeachtable ‘execute sp_spaceused [?];’;
go
脚本2扩展了上一个脚本的参数使用。在@pre命令里它创建一个临时表来保存sp_spaceused返回的结果集，然后用@cmd1来更新表的统计，@cmd2用来插入临时表。除此以外，它还通过@whereand过滤条件来缩小范围，只针对humanresources这个schema下的表。最后在@post命令中读取临时表并删除它。
script #2 : sp_msforeachtable system stored procedure
–creates a temporary table to hold the resultsets
–returned by sp_spaceused and before calling it,
–it updates the statistics for each table
–filter out tables of humanresources schema only
execute sp_msforeachtable
@precommand = ‘create table ##results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)’,
@command1 = ‘update statistics ?;’,
@command2 = ‘insert into ##results execute sp_spaceused [?];’,
@whereand = ‘and schema_name(schema_id) = ”humanresources”’,
@postcommand = ‘select * from ##results; drop table ##results’
go
sp_msforeachtable默认使用objectproperty(o.id, n”isusertable”) = 1作为where条件，即只针对用户表进行操作。你可以通过@whereand加入系统表、视图、存储过程或者所有这些以及其他对象。例如在以下的脚本3中，语句1在上面脚本基础上加入了系统表，即对象既包括用户表也包括系统表。在语句2中，分别只显示视图和存储过程的定义。
script #3 : sp_msforeachtable system stored procedure
–creates a temporary table to hold the resultsets
–returned by sp_spaceused and before calling it,
–it updates the statistics for each table
–note it consider both user and system tables
execute sp_msforeachtable
@precommand = ‘create table ##results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)’,
@command1 = ‘update statistics ?;’,
@command2 = ‘insert into ##results execute sp_spaceused [?];’,
@whereand = ‘or objectproperty(o.id, n”issystemtable”) = 1′,
@postcommand = ‘select * from ##results; drop table ##results’
go
use adventureworks
go
–display the views’ script text
execute sp_msforeachtable
@command1 = ‘sp_helptext [?];’,
@whereand = ‘and objectproperty(o.id, n”isusertable”) = 0
or objectproperty(o.id, n”isview”) = 1′
go
use adventureworks
go
–display the stored procedures’ script text
execute sp_msforeachtable
@command1 = ‘sp_helptext [?];’,
@whereand = ‘and objectproperty(o.id, n”isusertable”) = 0
or objectproperty(o.id, n”isprocedure”) = 1′
go
脚本4演示了sp_msforeachdb的用法。语句1对所有db运行dbcc checkdb，以检查所有对象的分配、逻辑和物理上的结构性完整度。语句2首先过滤系统数据库，再对所有用户数据库实施备份。
script #4 : sp_msforeachdb system stored procedure
–checks the allocation, logical and physical structural
–integrity of all the objects of all the databases
exec sp_msforeachdb
@command1 = ‘dbcc checkdb([?])’
go –does backup of all the databases except system databases
declare @cmd1 nvarchar(2000)
set @cmd1 = ‘if ”?” not in(”master”, ”model”, ”tempdb”, ”msdb”)’ + ‘begin ‘
+ ‘print ”backing up ? database…”;’
+ ‘backup database [?] to disk=”’ + ‘d:\?_’ + replace(convert(varchar,getdate(),120),’:',”) + ‘.bak”’
+ ‘end’
exec sp_msforeachdb
@command1 = @cmd1
go
所有未公开的系统存储过程可能会在无通知的情况下变化，所以计划时需要考虑到这些。