关于sp_msforeachtable

[zksjjslq]

系统存贮过程sp_msforeachtable真是个好东西，就是不太了解，我在网上搜一搜内容大致都是一个样，看不大明白。主要是以下问题:1\关于参数中““和''的用法问题，何时用”何时用'搞得我头大;2\whereand条件中的一些字段是sysobjects表的字段吗？3\一个复杂的SQL语句能分散在@command1,@command2,@command3中吗？哪位高手指点一下啊？最好举些新的例子，不胜感激!

[fcuandy]

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  

用法我就不粘了，贴个地址给你。
http://blog.csdn.net/sxycgxj/archive/2007/01/27/1495568.aspx

[fcuandy]

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  

用法我就不粘了，贴个地址给你。
http://blog.csdn.net/sxycgxj/archive/2007/01/27/1495568.aspx

[Leshami]

楼主提的这个问题,我也是没怎么搞清楚
set quoted_identifier on
go
exec sp_MSforeachtable @command1="sp_spaceused '?'"
set quoted_identifier off
go
exec sp_MSforeachtable @command1="sp_spaceused '?'"
设定quoted_identifier 为on 和off好像都没有影响？
困惑？？？？