CREATE
proc
sp_MSforeachObject
@objectType int = 1 ,
@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
/*
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4
1 'IsUserTable' 表
2 'IsView' 视图
3 'IsTrigger' 触发器
4 'IsProcedure' 存储过程
5 'IsDefault'
6 'IsForeignKey' 外键
7 'IsScalarFunction' 自定义函数
8 'IsInlineFunction'
9 'IsPrimaryKey'
10 'IsExtendedProc' 主键
11 'IsReplProc'
12 'IsRule' 自定义规则
*/
/* 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 )
/* Defined @isobject for save object type */
Declare @isobject varchar ( 256 )
select @isobject = case @objectType
when 1 then ' IsUserTable '
when 2 then ' IsView '
when 3 then ' IsTrigger '
when 4 then ' IsProcedure '
when 5 then ' IsDefault '
when 6 then ' IsForeignKey '
when 7 then ' IsScalarFunction '
when 8 then ' IsInlineFunction '
when 9 then ' IsPrimaryKey '
when 10 then ' IsExtendedProc '
when 11 then ' IsReplProc '
when 12 then ' IsRule '
end
/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
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 ''' + @isobject + ''' ) = 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
@objectType int = 1 ,
@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
/*
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4
1 'IsUserTable' 表
2 'IsView' 视图
3 'IsTrigger' 触发器
4 'IsProcedure' 存储过程
5 'IsDefault'
6 'IsForeignKey' 外键
7 'IsScalarFunction' 自定义函数
8 'IsInlineFunction'
9 'IsPrimaryKey'
10 'IsExtendedProc' 主键
11 'IsReplProc'
12 'IsRule' 自定义规则
*/
/* 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 )
/* Defined @isobject for save object type */
Declare @isobject varchar ( 256 )
select @isobject = case @objectType
when 1 then ' IsUserTable '
when 2 then ' IsView '
when 3 then ' IsTrigger '
when 4 then ' IsProcedure '
when 5 then ' IsDefault '
when 6 then ' IsForeignKey '
when 7 then ' IsScalarFunction '
when 8 then ' IsInlineFunction '
when 9 then ' IsPrimaryKey '
when 10 then ' IsExtendedProc '
when 11 then ' IsReplProc '
when 12 then ' IsRule '
end
/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
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 ''' + @isobject + ''' ) = 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