• 系统存储过程sp_MSforeachtable的使用说明


    系统存储过程sp_MSforeachtable


    1.简介:
         作为DBA会经常需要检查所有的数据库或用户表,比如:检查所有数据库的容量;看看指定数据库所有用户表的容量,所有表的记录数,我们一般处理这样的问题都是用游标分别处理处理,比如:在数据库检索效率非常慢时,我们想检查数据库所有的用户表,我们就必须通过写游标来达到要求;如果我们用sp_MSforeachtable就可以非常方便的达到相同的目的:EXEC sp_MSforeachtable @command1
    ="print '?' DBCC CHECKTABLE ('?')"
         系统存储过程sp_MSforeachtable和sp_MSforeachdb,是微软提供的两个不公开的存储过程,从mssql6.5开始。存放在SQL Server的MASTER数据库中。可以用来对某个数据库的所有表或某个SQL服务器上的所有数据库进行管理,后面将对此进行详细介绍。

    2.参数说明:
       @command1 nvarchar(
    2000),                      --第一条运行的SQL指令
       @replacechar nchar(
    1) = N'?',                      --指定的占位符号
       @command2 nvarchar(
    2000= null,            --第二条运行的SQL指令
       @command3 nvarchar(
    2000= null,            --第三条运行的SQL指令
       @whereand nvarchar(
    2000= null,               --可选条件来选择表
       @precommand nvarchar(
    2000= null,        --执行指令前的操作(类似控件的触发前的操作)
       @postcommand nvarchar(
    2000= null       --执行指令后的操作(类似控件的触发后的操作)

       以后为sp_MSforeachtable的参数,sp_MSforeachdb不包括参数@whereand

    3.使用举例:

       
    --统计数据库里每个表的详细情况:
       exec sp_MSforeachtable @command1
    ="sp_spaceused '?'"

       
    --获得每个表的记录数和容量:
       EXEC sp_MSforeachtable @command1
    ="print '?'",
            @command2
    ="sp_spaceused '?'",
            @command3
    = "SELECT count(*) FROM ? "

       
    --获得所有的数据库的存储空间:
       EXEC sp_MSforeachdb   @command1
    ="print '?'",
            @command2
    ="sp_spaceused "

       
    --检查所有的数据库
       EXEC sp_MSforeachdb   @command1
    ="print '?'",
            @command2
    ="DBCC CHECKDB (?) "

       
    --更新PUBS数据库中已t开头的所有表的统计:
       EXEC sp_MSforeachtable @whereand
    ="and name like 't%'",
            @replacechar
    ='*',
            @precommand
    ="print 'Updating Statistics..' print ''",
            @command1
    ="print '*' update statistics * ",
            @postcommand
    = "print''print 'Complete Update Statistics!'"

       
    --删除当前数据库所有表中的数据
       sp_MSforeachtable @command1
    ='Delete from ?'
       sp_MSforeachtable @command1 
    = "TRUNCATE TABLE ?"

    4.参数@whereand的用法:


       @whereand参数在存储过程中起到指令条件限制的作用,具体的写法如下:
       @whereend,可以这么写 @whereand
    =' AND o.name in (''Table1'',''Table2'',.)'
       例如:我想更新Table1
    /Table2/Table3中NOTE列为NULL的值
       sp_MSforeachtable @command1
    ='Update ? Set NOTE='''' Where NOTE is NULL',@whereand=' AND o.name in (''Table1'',''Table2'',''Table3'')'

    5."?"在存储过程的特殊用法,造就了这两个功能强大的存储过程.

        这里
    "?"的作用,相当于DOS命令中、以及我们在WINDOWS下搜索文件时的通配符的作用。

    6.小结


       有了上面的分析,我们可以建立自己的sp_MSforeachObject:(转贴)
    USE MASTER
    GO
    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
    /* 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(int0x0002)))
    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

    这样我们来测试一下:
        
    --获得所有的存储过程的脚本:
              EXEc sp_MSforeachObject @command1
    ="sp_helptext '?' ",@objectType=4
        
    --获得所有的视图的脚本:
              EXEc sp_MSforeachObject @command1
    ="sp_helptext '?' ",@objectType=2
        
    --比如在开发过程中,没一个用户都是自己的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.
  • 相关阅读:
    su 命令切换用户带来的问题
    系统无法启动
    Linux单用户模式
    反射
    propety/静态方法
    内置方法,序列化模块
    第1月5周3天
    第1月4周5天
    第1月4周4日
    第1月4周3日
  • 原文地址:https://www.cnblogs.com/mincyw/p/1299884.html
Copyright © 2020-2023  润新知