SQL Server 使用游標取得所有使用者資料表資訊（含列數、建立日期、筆數）

需求場景

DBA 常需快速掌握資料庫中所有使用者資料表的基本資訊：表名、建立日期、資料筆數、空間使用情況。本腳本以游標逐一處理，彙整至暫存表輸出。

完整 T-SQL 腳本

/* 取得使用者資料表資訊：表名、建立日期、資料筆數 */
SET NOCOUNT ON;

DECLARE find_user_table CURSOR FOR
SELECT [name], crdate
FROM sysobjects
WHERE type = 'U'  -- 僅使用者資料表
ORDER BY [name];

DECLARE @cName VARCHAR(128);      -- 表名
DECLARE @crdate DATETIME;         -- 建立日期
DECLARE @Rows INT;                -- 筆數

-- 暫存表儲存結果
CREATE TABLE #tmpTable (
    Tablename VARCHAR(128),
    crDate DATETIME,
    RowCount INT
);

OPEN find_user_table;
FETCH NEXT FROM find_user_table INTO @cName, @crdate;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 動態 SQL 計算筆數 (參數化防注入)
    DECLARE @cSql NVARCHAR(500), @par NVARCHAR(30);
    SELECT @par = '@nRows INT OUTPUT';
    SELECT @cSql = 'SELECT @nRows = COUNT(*) FROM [' + @cName + ']';
    
    EXECUTE sp_executesql @cSql, @par, @Rows OUTPUT;
    
    -- 寫入暫存表
    INSERT INTO #tmpTable VALUES (@cName, @crdate, @Rows);
    
    FETCH NEXT FROM find_user_table INTO @cName, @crdate;
END

-- 輸出結果
SELECT * FROM #tmpTable ORDER BY RowCount DESC;

-- 清理資源
DROP TABLE #tmpTable;
CLOSE find_user_table;
DEALLOCATE find_user_table;

關鍵技術點解析

進階擴充：整合空間使用資訊

搭配 sp_spaceused 或查詢 sysindexes 可同時取得空間資訊：

-- 在游標迴圈中替換筆數計算區塊
CREATE TABLE #space (rows INT, reserved VARCHAR(20), data VARCHAR(20), 
                     index_size VARCHAR(20), unused VARCHAR(20));
INSERT #space EXEC sp_spaceused @cName;

INSERT INTO #tmpTable 
SELECT @cName, @crdate, 
       CAST(REPLACE((SELECT reserved FROM #space), ' KB', '') AS INT) AS ReservedKB,
       CAST(REPLACE((SELECT data FROM #space), ' KB', '') AS INT) AS DataKB;
-- 需調整 #tmpTable 結構

現代化替代方案 (SQL Server 2005+)

使用目錄檢視與 DMV，單一查詢即可完成，效能更佳：

SELECT 
    t.name AS TableName,
    t.create_date AS CreateDate,
    p.rows AS RowCount,
    (a.total_pages * 8) / 1024.0 AS ReservedMB,
    (a.used_pages * 8) / 1024.0 AS UsedMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.index_id IN (0,1)  -- 堆積或叢集索引
GROUP BY t.name, t.create_date, p.rows, a.total_pages, a.used_pages
ORDER BY ReservedMB DESC;

最佳實踐建議

• 大型資料庫 (<10k 資料表) 游標效能可接受，超大型建議改用集合式查詢
• 生產環境執行前先在測試庫驗證，避免長時間鎖定系統表
• 定期排程執行並存入管理資料表，支援容量規劃趨勢分析

此腳本為 DBA 日常巡檢、容量規劃、異常偵測提供快速、可靠的資料表概覽能力。