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