sp_MSforeachtable的作用

/*********************************************************************************
　sp_MSforeachtable 系统存储过程有7个参数:
　　@command1 nvarchar(2000), --第一条运行的t-sql指令
　　@replacechar nchar(1) = n'?', --指定的占位符号
　　@command2 nvarchar(2000) = null,--第二条运行的t-sql指令
　　@command3 nvarchar(2000) = null, --第三条运行的t-sql指令
　　@whereand nvarchar(2000) = null, --可选条件来选择表
　　@precommand nvarchar(2000) = null, --在sp_MSforeach_worker前执行的指令
　　@postcommand nvarchar(2000) = null --在sp_MSforeach_worker后执行的指令
************************************************************************************/

create proc sp_MSforeachtable
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */

/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
exec(@precommand)

/* Create the select */
exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
+ @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)
exec(@postcommand)

return @retval



Useage:

exec sp_msforeachtable @command1="print '?' dbcc checktable('?')"
上面的语句也可以这样写:
　　exec sp_msforeachtable @command1="print '?'",
　　@command2= "dbcc checktable('?')"

　1.获得每个表的记录数和容量:
　　exec sp_msforeachtable @command1="print '?'",
　　@command2="sp_spaceused '?'",
　　@command3= "select count(*) from ? "

　2.更新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!'"

3.批量更改SQL Server数据库中的用户对象
exec sp_msforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '

4。获得所有的存储过程的脚本：
EXEc sp_MSforeachObject @command1=“sp_helptext '?' “，@objectType=4

5。获得所有的视图的脚本：
EXEc sp_MSforeachObject @command1=“sp_helptext '?' “，@objectType=2

6。比如在开发过程中，没一个用户都是自己的OBJECT OWNER，所以在真实的数据库时都要改为DBO：
EXEc sp_MSforeachObject @command1=“sp_changeobjectowner '?'， 'dbo'“，@objectType=1
EXEc sp_MSforeachObject @command1=“sp_changeobjectowner '?'， 'dbo'“，@objectType=2
EXEc sp_MSforeachObject @command1=“sp_changeobjectowner '?'， 'dbo'“，@objectType=3
EXEc sp_MSforeachObject @command1=“sp_changeobjectowner '?'， 'dbo'“，@objectType=4
这样就非常方便的将每一个数据库对象改为DBO。

与exec sp_msforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '功用类似