最近做數據監控遇到這麼個查詢需求,就從系統存儲過程[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,根據索引的相關數據來獲得表的記錄條數: