SQL Server统计各用户表记录条数两秘法

2010-11-05 17:12:51 来源：博客抢沙发

2010-11-05 17:12:51 来源：博客

摘要：近做数据监控遇到这么个查询需求，就从系统存储过程[sys].[sp_tables]中征用了遍历用户表的代码，组织一下，配合以MSSQL 中的表变量，写了如下代码：
关键词： 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个时间片，性能大为胜出。

第三十五届CIO班招生
国际CIO认证培训
首席数据官（CDO）认证培训

责编：lyre

免责声明：本网站（http://www.ciotimes.com/）内容主要来自原创、合作媒体供稿和第三方投稿，凡在本网站出现的信息，均仅供参考。本网站将尽力确保所提供信息的准确性及可靠性，但不保证有关资料的准确性及可靠性，读者在使用前请进一步核实，并对任何自主决定的行为负责。本网站对有关资料所引致的错误、不确或遗漏，概不负任何法律责任。
本网站刊载的所有内容（包括但不仅限文字、图片、LOGO、音频、视频、软件、程序等）版权归原作者所有。任何单位或个人认为本网站中的内容可能涉嫌侵犯其知识产权或存在不实内容时，请及时通知本站，予以删除。