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'' '  功用类似