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個時間片，性能大為勝出。

關鍵詞：統計  記錄  用戶  數據表  對比