SQL Server 統計所有使用者表筆數的兩種方法與效能對比 (上)

方法一：游標遍歷 + 動態 SQL 逐表 COUNT(*) (精確但慢)

DECLARE @NAME VARCHAR(50), @SQL VARCHAR(MAX);

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);

生成的動態 SQL 範例

DECLARE @RESULT_TABLE TABLE ([TableName] VARCHAR(32), [RowCount] INT);
DECLARE @TEMP_COUNT INT;
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;
...
SELECT * FROM @RESULT_TABLE;

設計考量

• 使用表變數而非暫存表：筆數 = 表數量 (通常 < 1000)，完全在記憶體運作，避免 tempdb 壓力。
• 利用 HAS_PERMS_BY_NAME 過濾無 SELECT 權限的表，避免動態 SQL 執行報錯。
• 來源視圖：sys.all_objects 篩選 type = 'U'。

效能瓶頸與改進方向

• 游標循環 + 動態 SQL 編譯/執行開銷大。
• 逐表 COUNT(*) 觸發掃描，大表耗時久。
• 下篇將介紹 sys.sysobjects + sys.sysindexes 集合式查詢，CPU 開銷降低 30 倍。

作者：Lance Zhang | 出處：https://blodfox777.cnblogs.com/ | 版權歸作者與博客園共有。