不用游标来循环访问SQLSERVER数据库对象

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

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

所有未公开的系统存储过程可能会在无通知的情况下变化，所以计划时需要考虑到这些。