sp_ForEachTable Code:

drop proc sp__foreachtable
go

create proc sp__foreachtable

/***	Built from the Microsoft original sp__msForEachTable
****
****	Use at your own risk
***/

  @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 (object_name(id))
      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

Usage examples


sp__foreachtable 'dbcc showcontig(?)'  /*** SQL 2000 only ***/
sp__foreachtable "dbcc dbreindex(?)"
sp__foreachtable 'sp_recompile ?'