sp_MSforeachtable 与 sp_MSforeachdb


在MSSQL里有许多不公开的系统存储过程,其中可能常用的sp_MSforeachtable和sp_MSforeachdb有这2个。
分别用于遍历某数据库的每个用户表、每个数据库。

sp_MSforeachtable


create   proc  sp_MSforeachtable
    
@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    --在表后执行的指令
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 )
    --上面的代码,就是定义游标根据系统表sysobjects获取用户表,@whereand就确定 sysobjects的where条件
    
declare   @retval   int
    
select   @retval   =   @@error
    
if  ( @retval   =   0 )
        --调用sp_MSforeach_worker 执行游标遍历
        --sp_MSforeach_worker存储过程见最后
        
exec   @retval   =  sp_MSforeach_worker  @command1 @replacechar @command2 @command3

    
if  ( @retval   =   0   and   @postcommand   is   not   null )
        
exec ( @postcommand )

    
return   @retval

go
--统计数据库里每个表的详细情况
exec sp_MSforeachtable 'sp_spaceused ''?'''
--获得每个表的记录数和容量
exec sp_MSforeachtable 'select ''?''','?', 'sp_spaceused ''?''', 'SELECT count(*) FROM ? '
--获得所有的数据库的存储空间
exec sp_MSforeachdb  'select  ''?''','?','sp_spaceused '
--检查所有的数据库
exec sp_MSforeachdb  @command1="print '?'",'DBCC CHECKDB (?) '
--更新PUBS数据库中已t开头的所有表的统计
exec sp_MSforeachtable
       'print ''*'' update statistics * ',
       '*',
       null,
       null,
       ' and name like ''t%''',
       'print ''Updating Statistics.....''',
       'print ''Complete Update Statistics!'''

--删除当前数据库所有表中的数据
sp_MSforeachtable 'Delete from ?'
sp_MSforeachtable 'Truncate Table ?'

--更新Table1/Table2中note列为NULL的值
sp_MSforeachtable 'Update ? Set note='''' Where note is null',null,null,null,' AND o.name in (''Table1'',''Table2'')



sp_MSforeachdb 在应用上与sp_MSforeachTable一样,只是没有@whereand 这个条件变量而已
/*
 * The following table definition will be created by SQLDMO at start of each connection.
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
 
*/
Create   proc  sp_MSforeachdb
    
@command1   nvarchar ( 2000 ), 
    
@replacechar   nchar ( 1 =  N ' ? '
    
@command2   nvarchar ( 2000 =   null
    
@command3   nvarchar ( 2000 =   null ,
    
@precommand   nvarchar ( 2000 =   null
    
@postcommand   nvarchar ( 2000 =   null
as
   
set  deadlock_priority low
    
/*  This proc returns one or more rows for each accessible db, with each db 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   @inaccessible   nvarchar ( 12 ),  @invalidlogin   nvarchar ( 12 ),  @dbinaccessible   nvarchar ( 12 )
    
select   @inaccessible   =   ltrim ( str ( convert ( int 0x03e0 ),  11 ))
    
select   @invalidlogin   =   ltrim ( str ( convert ( int 0x40000000 ),  11 ))
    
select   @dbinaccessible   =  N ' 0x80000000 '          /*  SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert()  */

    
if  ( @precommand   is   not   null )         exec ( @precommand )

    
declare   @origdb   nvarchar ( 128 )
    
select   @origdb   =   db_name ()

    
/*  If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it.  */
    
/*  Create the select  */
    
exec (N ' declare hCForEach cursor global for select name from master.dbo.sysdatabases d  '   +
            N
'  where (d.status &  '   +   @inaccessible   +  N '  = 0) '   +
            N
'  and ((DATABASEPROPERTY(d.name,  '' issingleuser '' ) = 0 and (has_dbaccess(d.name) = 1)) or  '   +
            N
'  ( DATABASEPROPERTY(d.name,  '' issingleuser '' ) = 1 and not exists  '   +
            N
'  (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid))) '  )

    
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 )

    
declare   @tempdb   nvarchar ( 258 )
       
SELECT   @tempdb   =   REPLACE ( @origdb , N ' ] ' , N ' ]] ' )
       
exec  (N ' use  '   +  N ' [ '   +   @tempdb   +  N ' ] ' )

    
return   @retval

go


sp_MSforeach_worker
/*
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach"
 * has already been opened by its caller.
 
*/
create   proc  sp_MSforeach_worker
    
@command1   nvarchar ( 2000 ), 
    
@replacechar   nchar ( 1 =  N ' ? '
    
@command2   nvarchar ( 2000 =   null
    
@command3   nvarchar ( 2000 =   null
as

    
create   table  #qtemp (     /*  Temp command storage  */
        qnum                
int                  NOT   NULL ,
        qchar                
nvarchar ( 2000 )    COLLATE database_default  NULL
    )

    
set  nocount  on
    
declare   @name   nvarchar ( 517 ),  @namelen   int @q1   nvarchar ( 2000 ),  @q2   nvarchar ( 2000 )
    
declare   @q3   nvarchar ( 2000 ),  @q4   nvarchar ( 2000 ),  @q5   nvarchar ( 2000 )
    
declare   @q6   nvarchar ( 2000 ),  @q7   nvarchar ( 2000 ),  @q8   nvarchar ( 2000 ),  @q9   nvarchar ( 2000 ),  @q10   nvarchar ( 2000 )
    
declare   @cmd   nvarchar ( 2000 ),  @replacecharindex   int @useq   tinyint @usecmd   tinyint @nextcmd   nvarchar ( 2000 )
   
declare   @namesave   nvarchar ( 517 ),  @nametmp   nvarchar ( 517 ),  @nametmp2   nvarchar ( 258 )

    
open  hCForEach
    
fetch  hCForEach  into   @name
    
/*  Loop for each database  */
    
while  ( @@fetch_status   >=   0
    
begin
        
/*  Initialize.  */
        
/*  save the original dbname  */
       
select   @namesave   =   @name
        
select   @useq   =   1 @usecmd   =   1 @cmd   =   @command1 @namelen   =   datalength ( @name )
        
while  ( @cmd   is   not   null )
        
begin          /*  Generate @q* for exec()  */
            
/*
             * Parse each @commandX into a single executable batch.
             * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
             * We also may append @commandX's (signified by '++' as first letters of next @command).
             
*/
            
select   @replacecharindex   =   charindex ( @replacechar @cmd )
            
while  ( @replacecharindex   <>   0
            
begin

                    
/*  7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname  */
                    
/*  if the name has not been single quoted in command, do not doulbe them  */
                    
/*  if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname  */
                    
select   @name   =   @namesave
                    
select   @namelen   =   datalength ( @name )
                    
declare   @tempindex   int
                    
if  ( substring ( @cmd @replacecharindex   -   1 1 =  N ''''
                    
begin
                       
/*  if ? is inside of '', we need to double all the ' in name  */
                       
select   @name   =   REPLACE ( @name , N '''' , N '''''' )
                    
end  
                   
else   if  ( substring ( @cmd @replacecharindex   -   1 1 =  N ' [ '
               
    begin
                       
/*  if ? is inside of [], we need to double all the ] in name  */
                       
select   @name   =   REPLACE ( @name , N ' ] ' , N ' ]] ' )
                    
end  
                   
else   if  (( @name   LIKE  N ' %].%] ' and  ( substring ( @name 1 1 =  N ' [ ' )) 
                   
begin
                       
/*  ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it  */
                       
/*  !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char  */
                       
select   @tempindex   =   charindex (N ' ].[ ' @name )
                       
select   @nametmp    =   substring ( @name 2 @tempindex - 2  )
                       
select   @nametmp2   =   substring ( @name @tempindex + 3 len ( @name ) - @tempindex - 3  )
                       
select   @nametmp    =   REPLACE ( @nametmp , N ' ] ' , N ' ]] ' )
                       
select   @nametmp2   =   REPLACE ( @nametmp2 , N ' ] ' , N ' ]] ' )
                       
select   @name   =  N ' [ '   +   @nametmp   +  N ' ].[ '   +   @nametmp2   +   ' ] '
                    
end  
                   
else   if  (( @name   LIKE  N ' %] ' and  ( substring ( @name 1 1 =  N ' [ ' )) 
                   
begin
                       
/*  ? is NOT inside of [] nor '', and the name is in [name] format, handle it  */
                       
/*  j.i.c., since we should not fall into this case  */
                       
/*  !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char  */
                        
select   @nametmp   =   substring ( @name 2 len ( @name ) - 2  )
                        
select   @nametmp   =   REPLACE ( @nametmp , N ' ] ' , N ' ]] ' )
                        
select   @name   =  N ' [ '   +   @nametmp   +  N ' ] '
                    
end
                    
/*  Get the new length  */
                    
select   @namelen   =   datalength ( @name )
                    
/*  start normal process  */
                   
if  ( datalength ( @cmd +   @namelen   -   1   >   2000
                   
begin
               
    /*  Overflow; put preceding stuff into the temp table  */
                       
if  ( @useq   >   9
                       
begin
                           
raiserror   55555  N ' sp_MSforeach_worker assert failed:  command too long '
                           
close  hCForEach deallocate  hCForEach
                           
return   1
                       
end
               
        if  ( @replacecharindex   <   @namelen begin
                       
/*  If this happened close to beginning, make sure expansion has enough room.  */
                       
/*  In this case no trailing space can occur as the row ends with @name.  */
                       
select   @nextcmd   =   substring ( @cmd 1 @replacecharindex )
                       
select   @cmd   =   substring ( @cmd @replacecharindex   +   1 2000 )
                       
select   @nextcmd   =   stuff ( @nextcmd @replacecharindex 1 @name )
                   
    select   @replacecharindex   =   charindex ( @replacechar @cmd )
                       
insert  #qtemp  values  ( @useq @nextcmd )
                       
select   @useq   =   @useq   +   1
                       
continue
                   
end
               
    /*  Move the string down and stuff() in-place.  */
                   
/*  Because varchar columns trim trailing spaces, we may need to prepend one to the following string.  */
                   
/*  In this case, the char to be replaced is moved over by one.  */
                   
insert  #qtemp  values  ( @useq substring ( @cmd 1 @replacecharindex   -   1 ))
                   
if  ( substring ( @cmd @replacecharindex   -   1 1 =  N '   '
                   
begin
                       
select   @cmd   =  N '   '   +   substring ( @cmd @replacecharindex 2000 )
                   
    select   @replacecharindex   =   2
                   
end  
                   
else  
                   
begin
                       
select   @cmd   =   substring ( @cmd @replacecharindex 2000 )
                       
select   @replacecharindex   =   1
                   
end
                   
select   @useq   =   @useq   +   1
               
end
           
    select   @cmd   =   stuff ( @cmd @replacecharindex 1 @name )
               
select   @replacecharindex   =   charindex ( @replacechar @cmd )
           
end
       
    /*  Done replacing for current @cmd.  Get the next one and see if it's to be appended.  */
           
select   @usecmd   =   @usecmd   +   1
           
select   @nextcmd   =   case  ( @usecmd when   2   then   @command2   when   3   then   @command3   else   null   end
       
    if  ( @nextcmd   is   not   null   and   substring ( @nextcmd 1 2 =  N ' ++ '
           
begin
               
insert  #qtemp  values  ( @useq @cmd )
               
select   @cmd   =   substring ( @nextcmd 3 2000 ),  @useq   =   @useq   +   1
           
    continue
           
end
           
/*  Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors.  */
       
    /*  Null them first as the no-result-set case won't.  */
          
select   @q1   =   null @q2   =   null @q3   =   null @q4   =   null @q5   =   null @q6   =   null @q7   =   null @q8   =   null @q9   =   null @q10   =   null
           
select   @q1   =  qchar  from  #qtemp  where  qnum  =   1
       
    select   @q2   =  qchar  from  #qtemp  where  qnum  =   2
           
select   @q3   =  qchar  from  #qtemp  where  qnum  =   3
           
select   @q4   =  qchar  from  #qtemp  where  qnum  =   4
       
    select   @q5   =  qchar  from  #qtemp  where  qnum  =   5
           
select   @q6   =  qchar  from  #qtemp  where  qnum  =   6
           
select   @q7   =  qchar  from  #qtemp  where  qnum  =   7
       
    select   @q8   =  qchar  from  #qtemp  where  qnum  =   8
           
select   @q9   =  qchar  from  #qtemp  where  qnum  =   9
           
select   @q10   =  qchar  from  #qtemp  where  qnum  =   10
       
    truncate   table  #qtemp
           
exec  ( @q1   +   @q2   +   @q3   +   @q4   +   @q5   +   @q6   +   @q7   +   @q8   +   @q9   +   @q10   +   @cmd )
           
select   @cmd   =   @nextcmd @useq   =   1
       
end  
   
    /*  while @cmd is not null, generating @q* for exec()  */
       
/*  All commands done for this name.  Go to next one.  */
       
fetch  hCForEach  into   @name
    end
    /*
 while FETCH_SUCCESS  */
    close  hCForEach deallocate  hCForEach
    return   0


其他一些sql中的扩展存储的总结:

xp_availablemedia    (无)      显示系统上可用的盘符'C:/' xp_availablemedia 
xp_enumgroups                     列出当前系统的使用群组及其说明 xp_enumgroups 
xp_enumdsn            (无)      列出系统上已经设置好的ODBC数据源名称 xp_enumdsn 
xp_dirtree            (无)      显示某个目录下的子目录与文件架构 xp_dirtree 'C:/inetpub/wwwroot/' 
xp_getfiledetails    (无)      获取某文件的相关属性 xp_getfiledetails 'C:/inetpub/wwwroot.asp' 
dbp.xp_makecab        (无)     将目标计算机多个档案压缩到某个档案里所压缩的档案都可以接在参数的后面用豆号隔开             dbp.xp_makecab 'C:/lin.cab','evil',1,'C:/inetpub/mdb.asp' 
xp_unpackcab           (无)      解压缩 xp_unpackcab 'C:/hackway.cab','C:/temp',1 
xp_ntsec_enumdomains  (无)      列出服务器域名 xp_ntsec_enumdomains 
xp_servicecontrol     (无)      停止或者启动某个服务 xp_servicecontrol 'stop','schedule' 
xp_terminate_process  (无)     用pid来停止某个执行中的程序 xp_terminate_process 123 
dbo.xp_subdirs        (无)      只列某个目录下的子目录 dbo.xp_subdirs 'C:/'


网络上有个存储过程 sp_MSforeachObject,这是根据sp_MSforeachtable延伸出来的一个存储过程,扩展了应用。个人感觉很有用,这里推荐下。
USE  MASTER
GO
-- =============================================================
--
@objectType 对象类型 
--
1  IsUserTable
--
2  IsView
--
3  IsTrigger
--
4  IsProcedure
--
5  IsDefault
--
6  IsForeignKey
--
7  IsScalarFunction
--
8  IsInlineFunction
--
9  IsPrimaryKey
--
10 IsExtendedProc
--
11 IsReplProc
--
12 IsRule
--
=============================================================
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 ( 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

--下面2个例子:第一个 所有存储过程源代码,第2个修改所有表的所有者为dbo
sp_MSforeachObject 
4 , ' sp_helptext  '' ? '''
sp_MSforeachObject 
1 , ' sp_changeobjectowner  '' ? '' '' dbo '''  --当然这个可以应用sp_MSforeachtable 来完成
  • 1
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

sp4

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值