SQL Server 中统计各用户表记录条数 的两种方法

最近做数据监控遇到这么个查询需求，就从系统存储过程[sys].[sp_tables]中征用了遍历用户表的代码，组织一下，配合以MSSQL 中的表变量，写了如下代码：

方法一：

     DECLARE   @NAME   VARCHAR ( 50 )
     DECLARE   @SQL   VARCHAR ( 1000 )
    
     SET   @SQL   =   '         
    DECLARE @RESULT_TABLE TABLE
    (
        [TableName]        VARCHAR(32),
        [RowCount]        INT
    )
    DECLARE @TEMP_COUNT INT '
    
     DECLARE  TB_CURSOR  CURSOR   FOR   

     SELECT
        TABLE_NAME       =   CONVERT (SYSNAME,O.NAME)
     FROM
        SYS.ALL_OBJECTS O
     WHERE
        O.TYPE  =   ' U '   AND
        HAS_PERMS_BY_NAME( QUOTENAME (SCHEMA_NAME(O.SCHEMA_ID))  +   ' . '   +   QUOTENAME (O.NAME),
                           ' OBJECT ' ,
                           ' SELECT ' )  =   1  
    
     OPEN  TB_CURSOR   
     FETCH   NEXT   FROM  TB_CURSOR  INTO   @NAME    

     WHILE   @@FETCH_STATUS   =   0    
     BEGIN    
            SET   @SQL   =   @SQL   +   CHAR ( 10 )  +   ' INSERT INTO @RESULT_TABLE SELECT  '   +   ''''   +   @NAME   +    ''''   +   ' ,COUNT(1) FROM  '   +   @NAME   +   ' ; '
        
            FETCH   NEXT   FROM  TB_CURSOR  INTO   @NAME    
     END

     CLOSE  TB_CURSOR
     DEALLOCATE  TB_CURSOR
    
     SET   @SQL   =   @SQL   +   CHAR ( 10 )  + ' SELECT * FROM @RESULT_TABLE  '
     EXEC  ( @SQL )
        

 

 这里使用表变量而非临时表，是因为大多数数据库中表的数量不会太多，使得临时表（或表变量）中的记录条数不会很多。如此一来，借以表变量，将数据暂时存放放在内存中要比存放在tempDB中更加高效。

 

基本思路为：

1.从系统视图SYS.ALL_OBJECTS中取出所有用户表的表名。

2.用游标遍历所有表名，并使用select count(1)来统计该表行数，并拼接成相应的暂存SQL代码。

3.执行生成的SQL代码，取得数据结果集。其中生成的SQL代码为：

     DECLARE   @RESULT_TABLE   TABLE
    (
         [ TableName ]          VARCHAR ( 32 ),
         [ RowCount ]          INT
    )
     DECLARE   @TEMP_COUNT   INT
    
     --  each tables
     INSERT   INTO   @RESULT_TABLE   SELECT   ' LDMMessage ' , COUNT ( 1 )  FROM  LDMMessage;
     INSERT   INTO   @RESULT_TABLE   SELECT   ' DCSFile ' , COUNT ( 1 )  FROM  DCSFile;
     INSERT   INTO   @RESULT_TABLE   SELECT   ' SSRCode ' , COUNT ( 1 )  FROM  SSRCode;
     INSERT   INTO   @RESULT_TABLE   SELECT   ' PRLMessage ' , COUNT ( 1 )  FROM  PRLMessage;
    ...
    
     SELECT   *   FROM   @RESULT_TABLE  

 

 写完之后，感觉毕竟使用到了游标和表变量，性能不太理想，应该还有更好的方法，便google了一下，发现也可以从系统视图SYS.SYSOBJECTS中查出用户表名，并使用主键ID连接视图SYS.SYSINDEXES，根据索引的相关数据来获得表的记录条数:

方法二：

     DECLARE   @RESULT_TABLE   TABLE
    (
         [ TableName ]          VARCHAR ( 32 ),
         [ RowCount ]          INT
    )

     INSERT   INTO  
         @RESULT_TABLE
     SELECT
        O.NAME, I.ROWCNT
     FROM
        SYS.SYSOBJECTS O, SYSINDEXES I 
     WHERE
        O.ID  =  I.ID  AND
        O.XTYPE  =   ' U '   AND
        I.INDID  <   2

     SELECT   *   FROM   @RESULT_TABLE
        

 

 这里主要使用了SYS.SYSOBJECTS和SYS.SYSINDEXES的连接，并通过 I.INDID < 2 条件找到表的聚集索引或堆记录（Heap：0， 聚集索引：1，非聚集索引>1），由此得出Data级别的记录条数RowCnt。

 

性能对比：

使用SQL Server Profiler来检测两种方法的执行开销，结果如下：

 

方法一开销62个CPU时间片，而方法二之开销了2个时间片，性能大为胜出。