zz两个存储过程

Sp_MSForEachTable和sp_MSForEachDb是SQL Server的两个系统存储过程，存在于Master数据库当中，但是在SQL Server的联机帮助中并没有这两个存储过程，也就是说微软并没有把这两个存储过程编进SQL Server的联机丛书中。那么Microsoft设计这两个系统存储过程的目的到底是什么呢？这两个系统存储过程又能干些什么呢？实际上Microsoft设计这两个系统存储过程的目的是要支持编写一种单一的SQL语句，该语句可以在当前服务器的所有数据库上或当前数据库的所有表上执行相同的动作。下面介绍这两个存储过程。

        首先看下这条SQL语句：exec sp_MSForEachDb @Command1 = “print ‘?’” ，该SQL语句从当前服务器中的所有的数据名称。执行结果如图：
                                   
@Command1参数用于指定存储过程将在每个数据库上执行的动作。这里使用问号代替了数据库名称，该存储过程最多可以指定三个命令（使用@Command2 、@Command3）。在后台，这个存储过程将在sysdatabases表中为每一条记录打开一个游标，然后动态组装一个将通过循环在每条记录上执行的批处理。这条语句将显示每个数据库中的用户表的数目：exec sp_MSForEachDb @Command1 = "select count(name) from ?.dbo.sysobjects where xtype='U'"，执行结果如图：
                       

下面的命令创建了一个有关每个数据库空间使用情况的报告：exec sp_MSForEachDb @Command1 = "use ? exec sp_Spaceused"，执行结果如图：
        

更为有趣的是可以在当前数据库的所有表上运行sp_spaceused过程：exec sp_MSForEachtable @Command1 = "sp_Spaceused '?'"，你也可以在每一个表上得到一些记录：exec sp_MSForEachtable @Command1 = "print '?'",@Command2 = "select count(*) from ?"，执行结果并没有按照所希望的顺序排列，如果你想按照表名对它进行排列，必须使用@whereand参数：exec sp_MSForEachtable @Command1 = "exec sp_Spaceused '?'",@whereand = " order by name"，这个参数本来是用来添加where子查询用的，但是因为查询时动态的组装的，所以你可以借用一下来加入一个Order by子句。

        再给你偷一个小窍门，也就是如果一个命令在循环之前或之后只应该执行一次，那么你可以使用@precommand或@postcommand参数；还有你也还可以使用@replacechar参数来为数据库名称和表名指定不同的占位符，这个参数在命令需要使用问号时十分有用，例如，Like子句中的通配符等。

        以前，在检查所有数据库的容量;看看指定数据库所有用户表的容量，所有表的记录数等工作时需要写一些游标来完成这些工作，不但费劲费时，而且游标的效率也不高，好了，有了这两个存储过程，你就可以轻而易举的完成这些工作了。大家感兴趣的话，可以去看看这两个存储过程的源代码，这里就不再把它贴出来了。

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=477211

ps: 网上的一个删除表的用法：

-- 禁用所有约束 
EXEC sp_msforeachtable 
@command1=N'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- 删除有数据R的表 
EXEC sp_msforeachtable 
@command1=N'DELETE ?', 
@whereand=N'AND EXISTS( 
SELECT rows FROM dbo.sysindexes 
WHERE id=O.id 
AND rows>0 
AND indid<2)' 

-- 启用所有约束 
EXEC sp_msforeachtable 
@command1=N'ALTER TABLE ? CHECK CONSTRAINT ALL' 

不过上面那个发现不能用，网上又找了个，用游标的，去除了表与表之间的外键约束，然后删掉。

Create proc [dbo].[DeleteTable](@tablename varchar(100))
                    as
       begin 
              declare   tb   cursor   for   
              select   s='alter   table '+ c.name+ ' drop   constraint '+  b.name  
              from   sysforeignkeys   a   
              join   sysobjects   b   on   b.id=a.constid   
              join   sysobjects   c   on   c.id=a.fkeyid   
              join   syscolumns   d   on   d.id=c.id and a.fkey=d.colid   
              join   sysobjects   e   on   e.id=a.rkeyid   
              join   syscolumns   f   on   f.id=e.id   and   a.rkey=f.colid     
              where   e.id=object_id(@tablename)   
                
              declare   @s   varchar(8000)   
              open   tb   
              fetch   next   from   tb   into   @s   
              while  @@fetch_status=0   
              begin   
              exec(@s)   
              fetch   next   from   tb   into   @s   
              end   
              close   tb   
              deallocate   tb 
             exec('drop table '+@tablename)
            end