專案需求:監控資料庫中所有使用者資料表的列數。作者提供兩種實作並實測效能,結果驚人。
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);
特點:使用表變數存放中繼結果,避免 tempdb 開銷;但游標逐列處理、動態 SQL 組合、大量 COUNT(*) 掃描,效能較差。
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; -- 0=Heap, 1=Clustered Index
SELECT * FROM @RESULT_TABLE;
原理:SYSINDEXES.ROWCNT 儲存資料列估計值(統計資料更新後較準確),INDID < 2 只取堆積或叢集索引,避免重複計算非叢集索引。
| 指標 | 方法一(游標) | 方法二(系統檢視) |
|---|---|---|
| CPU 時間片 | 62 | 2 |
| 邏輯讀取 | 極高(每表掃描) | 極低(僅讀系統表) |
| 執行時間 | 秒級 | 毫秒級 |
ROWCNT 為統計資料估計值,極高精度需求可搭配 DBCC UPDATEUSAGE 或 sp_spaceusedsys.dm_db_partition_stats 或 sys.tables 搭配 SUM(p.rows) 獲得更準確結果COUNT(*)善用系統目錄檢視,拒絕游標暴力枚舉,是 DBA 與開發者的基本功。