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个时间片，性能大为胜出。

 

参考资料：

http://msdn.microsoft.com/en-us/library/ms190324.aspx

http://msdn.microsoft.com/en-us/library/ms178618.aspx

http://msdn.microsoft.com/en-us/library/ms190283.aspx 

 

[sys].[sp_tables]

/* ***** Object:  StoredProcedure [sys].[sp_tables]    Script Date: 10/27/2010 14:06:19 ***** */
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO

ALTER   procedure   [ sys ] . [ sp_tables ]
(
     @table_name           nvarchar ( 384 )    =   null ,
     @table_owner          nvarchar ( 384 )    =   null ,
     @table_qualifier     sysname  =   null ,
     @table_type           varchar ( 100 )  =   null ,
     @fUsePattern          bit   =   1   --  To allow users to explicitly disable all pattern matching.
)
as
     declare   @type1        varchar ( 3 )
     declare   @qual_name    nvarchar ( 517 )  --  [schema].[table]
     declare   @table_id     int

     if   @table_qualifier   =   ' % '   and   @table_owner   =   ''   and   @table_name   =   ''
     begin
         --  Debug output, do not remove it.
         --  print 'Special feature #1:  enumerate databases when owner and name are blank but qualifier is explicitly "%".'
         select
            TABLE_QUALIFIER  =   convert (sysname,d.name),
            TABLE_OWNER      =   convert (sysname, null ),
            TABLE_NAME       =   convert (sysname, null ),
            TABLE_TYPE       =   convert ( varchar ( 32 ), null ),
            REMARKS          =   convert ( varchar ( 254 ), null )     --  Remarks are NULL.
         from
            sys.databases d
         where
            d.name  <>   ' model '   --  eliminate MODEL database
         order   by   1
         return
     end

     if   @table_qualifier   =   ''   and   @table_owner   =   ' % '   and   @table_name   =   ''
     begin
         --  Debug output, do not remove it.
         --  print 'Special feature #2:  enumerate owners when qualifier and name are blank but owner is explicitly "%".
         select   distinct
            TABLE_QUALIFIER  =   convert (sysname, null ),
            TABLE_OWNER      =   convert (sysname,schema_name(o.schema_id)),
            TABLE_NAME       =   convert (sysname, null ),
            TABLE_TYPE       =   convert ( varchar ( 32 ), null ),
            REMARKS          =   convert ( varchar ( 254 ), null )     --  Remarks are NULL.
         from
            sys.all_objects o
         where
            o.type  in  ( ' S ' , ' U ' , ' V ' )   --  limit columns to tables and views only
         order   by   2
         return
     end

     if   @table_qualifier   =   ''   and   @table_owner   =   ''   and   @table_name   =   ''   and   @table_type   =   ' % '
     begin
         --  Debug output, do not remove it.
         --  print 'Special feature #3:  enumerate table types when qualifier, owner and name are blank but table type is explicitly "%".'
         select
            TABLE_QUALIFIER  =   convert (sysname, null ),
            TABLE_OWNER      =   convert (sysname, null ),
            TABLE_NAME       =   convert (sysname, null ),
            TABLE_TYPE       =   convert ( varchar ( 32 ),
                                         rtrim ( substring ( ' SYSTEM TABLETABLE       VIEW ' ,(c.column_id - 1 ) * 12 + 1 , 12 ))),
            REMARKS          =   convert ( varchar ( 254 ), null )     --  Remarks are NULL.
         from
            sys.all_objects o,
            sys.all_columns c
         where
            o. object_id   =  c. object_id   and  o. object_id   =   object_id ( ' sysusers ' )  and
            c.column_id  <=   3   --  ISSUE - what is this for ???
         return
     end

     --
     --  End of special features - do normal processing.
     --

     if   @table_qualifier   is   not   null
     begin
         if   db_name ()  <>   @table_qualifier
         begin
             if   @table_qualifier   =   ''
             begin    --  If empty qualifier supplied, force an empty result set.
                 select   @table_name   =   ''
                 select   @table_owner   =   ''
             end
             else
             begin     --  If qualifier doesn't match current database.
                 raiserror  ( 15250 ,  - 1 , - 1 )
                 return
             end
         end
     end
     select   @table_qualifier   =   null   --  it's not needed anymore

     if   @table_type   is   null
     begin     --  Select all ODBC supported table types.
         select   @type1   =   ' SUV '
     end
     else
     begin
         --  TableType is case sensitive if CS server.
         if  ( charindex ( ''' SYSTEM TABLE ''' , @table_type )  <>   0 )
             select   @type1   =   ' S '   --  Add System Tables.
         else
             select   @type1   =   ''
         if  ( charindex ( ''' TABLE ''' , @table_type )  <>   0 )
             select   @type1   =   @type1   +   ' U '   --  Add User Tables.
         if  ( charindex ( ''' VIEW ''' , @table_type )  <>   0 )
             select   @type1   =   @type1   +   ' V '   --  Add Views.
     end

     if   @table_name   is   not   null
     begin
         if  ( @table_owner   is   null )  and  ( charindex ( ' % ' ,  @table_name )  =   0 )
         begin     --  If owner not specified and table contains wildchar.
             if   exists
            (
                 select
                         *
                 from
                        sys.all_objects o
                 where
                        o.schema_id  =  schema_id()  and
                        o. object_id   =   object_id ( @table_name )  and
                        o.type  in  ( ' U ' , ' V ' , ' S ' )
            )
             begin     --  Override supplied owner w/owner of table.
                 select   @table_owner   =  schema_name()
             end
         end
     end

     select   @qual_name   =   isnull ( quotename ( @table_owner ),  '' )  +   ' . '   +   quotename ( @table_name )
     select   @table_id   =   object_id ( @qual_name )

     if  ( @fUsePattern   =   1 )  --  Does the user want it?
     begin
         if  (( isnull ( charindex ( ' % ' ,  @table_name ), 0 )  =   0 )  and
            ( isnull ( charindex ( ' _ ' ,  @table_name ), 0 )  =   0 )  and
            ( isnull ( charindex ( ' % ' ,  @table_owner ), 0 )  =   0 )  and
            ( isnull ( charindex ( ' _ ' ,  @table_owner ), 0 )  =   0 )  and
            ( @table_id   is   not   null ))
         begin
             select   @fUsePattern   =   0   --  not a single wild char, so go the fast way.
         end
     end

     if   @fUsePattern   =   0
     begin
         /*  -- Debug output, do not remove it.
        print '*************'
        print 'There is NO pattern matching.'
        print @fUsePattern
        print isnull(@table_name, '@table_name = null')
        print isnull(@table_owner, '@table_owner = null')
        print isnull(@table_type, '@table_type = null')
        print isnull(@type1, '@type1 = null')
        print '*************'
         */
         select
            TABLE_QUALIFIER  =   convert (sysname, db_name ()),
            TABLE_OWNER      =   convert (sysname,schema_name(o.schema_id)),
            TABLE_NAME       =   convert (sysname,o.name),
            TABLE_TYPE       =   convert ( varchar ( 32 ),
                                         rtrim ( substring ( ' SYSTEM TABLE            TABLE       VIEW        ' ,
                                        ( ascii (o.type) - 83 ) * 12 + 1 , 12 ))   --  'S'=0,'U'=2,'V'=3
                                     ),
            REMARKS  =   convert ( varchar ( 254 ), null )     --  Remarks are NULL.

         from
            sys.all_objects o

         where
            o. object_id   =   @table_id   and
            o.type  in  ( ' S ' , ' U ' , ' V ' )  and
            has_perms_by_name( @qual_name ,  ' object ' ,  ' select ' )  =   1   and
             charindex ( substring (o.type, 1 , 1 ), @type1 )  <>   0   --  Only desired types.
         order   by   4 ,  1 ,  2 ,  3
     end
     else
     begin
         /*  -- Debug output, do not remove it.
        print '*************'
        print 'THERE IS pattern matching!'
        print @fUsePattern
        print isnull(@table_name, '@table_name = null')
        print isnull(@table_owner, '@table_owner = null')
        print isnull(@table_type, '@table_type = null')
        print isnull(@type1, '@type1 = null')
        print '*************'
         */
         select
            TABLE_QUALIFIER  =   convert (sysname, db_name ()),
            TABLE_OWNER      =   convert (sysname,schema_name(o.schema_id)),
            TABLE_NAME       =   convert (sysname,o.name),
            TABLE_TYPE       =   convert ( varchar ( 32 ),
                                         rtrim ( substring ( ' SYSTEM TABLE            TABLE       VIEW        ' ,
                                              ( ascii (o.type) - 83 ) * 12 + 1 ,
                                               12 ))   --  'S'=0,'U'=2,'V'=3
                                     ),
            REMARKS  =   convert ( varchar ( 254 ), null )     --  Remarks are NULL.

         from
            sys.all_objects o

         where
            o.type  in  ( ' S ' , ' U ' , ' V ' )  and
            has_perms_by_name( quotename (schema_name(o.schema_id))  +   ' . '   +   quotename (o.name),
                               ' object ' ,
                               ' select ' )  =   1   and
             charindex ( substring (o.type, 1 , 1 ), @type1 )  <>   0   and   --  Only desired types.
            ( @table_name    is   NULL   or  o.name  like   @table_name )  and
            ( @table_owner   is   NULL   or  schema_name(o.schema_id)  like   @table_owner )
         order   by   4 ,  1 ,  2 ,  3
     end