扩展sp_MSforeach(二)

　　sp_MSforeachview 视图：

USE MASTER
GO
if exists (select name from sysobjects
where name = 'sp_MSforeachview' AND type = 'P')
drop procedure sp_MSforeachview
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_MSforeachview
@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''IsView'') = 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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

　　sp_MSforeachfk 外键：

USE MASTER
GO
if exists (select name from sysobjects
where name = 'sp_MSforeachfk' AND type = 'P')
drop procedure sp_MSforeachfk
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_MSforeachfk
@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''IsForeignKey'') = 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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO