242.SP_MSFOREACHTABLE

╰+哭是因爲堅強的太久メ 2023-12-27 06:46 19阅读 0赞

sp_MSforeachtable
    这个系统存储过程有7个参数:
    @command1 nvarchar(2000),			--第一条运行的T-SQL指令
    @replacechar nchar(1) = N'?',		--指定的占位符号 
    @command2 nvarchar(2000) = null,	--第二条运行的T-SQL指令
    @command3 nvarchar(2000) = null,	--第三条运行的T-SQL指令
    @whereand nvarchar(2000) = null,	--可选条件来选择表
    @precommand nvarchar(2000) = null,	--在表前执行的指令
    @postcommand nvarchar(2000) = null	--在表后执行的指令
    
    
    使用示例:
    
    --1.获得每个表的记录数和容量:
    EXEC sp_MSforeachtable @command1="print '?'",
             @command2="sp_spaceused '?'",
             @command3= "SELECT count(*) FROM ? "
    
    --2.更新PUBS数据库中已t开头的所有表的统计:
    EXEC sp_MSforeachtable @whereand='and name like ''t%''',
             @replacechar='*',
             @precommand='print ''Updating Statistics.....''',
             @command1='print ''*'' update statistics * ',
             @postcommand= 'print ''Complete Update Statistics!'''
    
    
    ==========================================================================
    
    建立自己的sp_MSforeachObject For zj:
    
    USE MASTER
    GO
    
    CREATE proc sp_MSforeachObject
    @objectType nvarchar(500)='U',	--对象类型,可以是下列对象类型中的一种或组合： 
    								--C = CHECK 约束
    								--D = 默认值或 DEFAULT 约束
    								--F = FOREIGN KEY 约束
    								--L = 日志
    								--FN = 标量函数
    								--IF = 内嵌表函数
    								--P = 存储过程
    								--PK = PRIMARY KEY 约束（类型是 K）
    								--RF = 复制筛选存储过程
    								--S = 系统表
    								--TF = 表函数
    								--TR = 触发器
    								--U = 用户表
    								--UQ = UNIQUE 约束（类型是 K）
    								--V = 视图
    								--X = 扩展存储过程
    @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
    declare @mscat nvarchar(12)
    select @mscat = ltrim(str(convert(int, 0x0002)))
    
    if (@precommand is not null)
    	exec(@precommand)
    
    /* Create the select */
    /* Use @isobject variable isstead of IsUserTable string */
    set @objectType=''''+replace(@objectType,',',''',''')+''''
    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 o.xtype in('+@objectType+') '+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
    
    ===================================================================================
    
    USE MASTER 
    GO
    SP_HELPTEXT sp_MSforeachtable
    
    --下面时sp_MSforeachtable的原始代码
    
    CREATE proc sp_MSforeachtable
    @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''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
    go
    
    sp_MSforeachD B除了@whereand外,和 sp_MSforeachtable 的参数是一样的
    我们可以通过这个存储过程检测所有的数据库,比如:
    
    --2.检查所有的数据库
    EXEC sp_MSforeachdb  @command1='print ''?''',
    	@command2='DBCC CHECKDB (?) '
    
    ==============================================================================
    建立自己的sp_MSforeachObject:
    
    USE MASTER
    GO
    
    CREATE proc sp_MSforeachObject
    @objectType int=1, 				--对象类型,可以是下列对象类型中的一种或组合： 
    								--C = CHECK 约束
    								--D = 默认值或 DEFAULT 约束
    								--F = FOREIGN KEY 约束
    								--L = 日志
    								--FN = 标量函数
    								--IF = 内嵌表函数
    								--P = 存储过程
    								--PK = PRIMARY KEY 约束（类型是 K）
    								--RF = 复制筛选存储过程
    								--S = 系统表
    								--TF = 表函数
    								--TR = 触发器
    								--U = 用户表
    								--UQ = UNIQUE 约束（类型是 K）
    								--V = 视图
    								--X = 扩展存储过程
    @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
    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
    
    这样我们来测试一下:
    1.获得所有的存储过程的脚本:
             EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
    2.获得所有的视图的脚本:
             EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=2
    
    3.比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO:
    EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1
    EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2
    EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3
    EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4
    
      这样就非常方便的将每一个数据库对象改为DBO.
    
    当然还要很多非常好的功能,大家可以自己深入研究吧:-)
    
     
    
     
    
    
     
    
     
     
    
    对该文的评论 人气：137