SQLSERVER中统计所有表的记录数

   今天群里的一个朋友问了个问题: 如何把数据库里所有表的记录数都统计出来？
   由于以前我曾经试着写过一个存储过程,作用就是删除所有表的内容,思路是这样的:

   首先通过sysobjects表构造一个sql语句字符串'DELETE 表名',其中表名就是sysobjects中的name列,把这些DELETE语句字符串连接起来的方法一是通过游标,二则是直接利用如下语句:

   select @sql = @sql + 'DELETE ' + name from sysobjects where xtype='U';

   这是一个很有用的技巧,在合适的地方用会很大程度的优化语句运行速度.

   然后就是通过exec(@sql)执行该字符串.

   而把数据库所有表的记录数统计出来和这个思路几乎完全一样,不同的就是把'DELETE 表名' 改为'SELECT 表名,COUNT(1) FROM 表名',主要这点不同而已,如果构造完字符串并执行完毕,可以把结果输出到一个临时表,那么再统计所有记录数就轻而易举了.

    下面就是我写的一个语句:

 

declare   @sql   varchar ( 8000 ), @count   int , @step   int

set  nocount  on

-- @step越大运行速度越快，但如果太大会造成生成的sql字符串超出限制导致语句不完整出错

-- 建议为50

set   @step   =   50

if   object_id (N ' tempdb.db.#temp ' )  is   not   null

drop   table  # temp

create   table  # temp  (name sysname, count  numeric( 18 ))

if   object_id (N ' tempdb.db.#temp1 ' )  is   not   null

drop   table  #temp1

create   table  #temp1 (id  int   identity ( 1 , 1 ),name sysname)

insert   into  #temp1(name)

select  name  from  sysobjects  where  xtype  =   ' u ' ;

set   @count   =   @@rowcount   while   @count > 0

begin

set   @sql   =   ''

select   @sql   =   @sql   +   '  select  '''   +  name  +   ''' ,count(1) from  '   +  name  +   '  union '

from  #temp1  where  id  >   @count   -   @step   and  id  <=   @count

set   @sql   =   left ( @sql , len ( @sql )  -   len ( ' union ' ))

insert   into  # temp   exec  ( @sql )

set   @count   =   @count   -   @step

end

select   count ( count ) 总表数, sum ( count ) 总记录数  from  # temp

select   *   from  # temp   order   by   count ,name

set  nocount  off

 

    经过测试,该方法可以通过,不过有时候@step的值需要手动设置一下,@step=50应该就可以满足大部分数据库的需要了.如果表名都比较短的话,可以设置@step=80或者100.

    后来我又去上网搜索其他统计数据库所有表记录数的语句,发现了下面的方法:

 

create   table  #(id  int   identity  ,tblname  varchar ( 50 ),num  int )

declare   @name   varchar ( 30 )

declare  roy  cursor   for   select  name  from  sysobjects  where  xtype = ' U '

open  roy

fetch   next   from  roy  into   @name

while   @@fetch_status = 0

begin

declare   @i   int

declare   @sql   nvarchar ( 1000 )

set   @sql = ' select @n=count(1) from  ' + @name

exec  sp_executesql  @sql ,N ' @n int output ' , @i  output

insert   into  #  select   @name , @I

fetch   next   from  roy  into   @name

end

close  roy

deallocate  roy

select   *   from  #

 

    该方法用到了游标,如果数据库表很多的话速度可能会比较慢,但是该表不受表名长短影响,对所有数据库都适用.

 

    第三种方法,利用系统的对象表和索引表:

set  nocount  on
if   object_id (N ' tempdb.db.#temp ' )  is   not   null
   drop   table  # temp
create   table  # temp  (name sysname, count  numeric( 18 ))

insert   into  # temp
select  o.name,i.rows
from  sysobjects o,sysindexes i  
where  o.id = i.id  and  o.Xtype = ' U '   and  i.indid < 2

select   count ( count ) 总表数, sum ( count ) 总记录数  from  # temp
select   *   from  # temp
set  nocount  off

 

    该方法执行速度绝对最快,但是结果好象并不是太准确,稍微有一些偏差.所以如果对数据量比较大而且对统计结果要求比较低的,该方法绝对是第一选择.如果要求统计绝对准确的记录数而且表的数量比较多的话,个人感觉第一个方法应该是个不错的选择.

    第三个方法主要是利用了系统索引表sysindexes中索引ID indid<1的行中的rows列存有该表的行数这一特点.

    最后一种方法是利用隐藏未公开的系统存储过程 sp_MSforeachtable

CREATE   TABLE  # temp  (TableName  VARCHAR  ( 255 ), RowCnt  INT )
EXEC  sp_MSforeachtable  ' INSERT INTO #temp SELECT  '' ? '' , COUNT(*) FROM ? '
SELECT  TableName, RowCnt  FROM  # temp   ORDER   BY  TableName
DROP   TABLE  # temp

       从mssql6.5开始，微软提供了两个不公开，非常有用的系统存储过程 sp_MSforeachtable和 sp_MSforeachdb，用于遍历某个数据库的每个表和遍历DBMS管理下的每个数据库。

        sp_MSforeachtable和sp_MSforeachdb 的用法: