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
