扩展sp_MSforeach

浅析SQL SERVER一个没有公开的存储过程中介绍了sp_MSforeachtable的工作方式和工作原理。不过令人失望的是它们不能扩展到其它的对象（比如视图、存储过程、触发器等）。那我们就自己对手来完全扩展这些存储过程。

--sp_MSforeachsproc 存储过程
USE MASTER
GO

if exists (select name from sysobjects 
            where name = 'sp_MSforeachsproc' AND type = 'P')
    drop procedure sp_MSforeachsproc
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create proc sp_MSforeachsproc
    @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''IsProcedure'') = 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_MSforeachtrigger 触发器
USE MASTER
GO

if exists (select name from sysobjects 
            where name = 'sp_MSforeachtrigger' AND type = 'P')
    drop procedure sp_MSforeachtrigger
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create proc sp_MSforeachtrigger
    @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''IsTrigger'') = 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_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