實用預存程序：列出所有使用者資料表空間使用情況 (含筆數、資料/索引/未用空間)

需求背景

DBA 需快速掌握資料庫中各資料表的空間佔用情況，識別大表、碎片嚴重表、無用空間過多表，作為容量規劃、索引維護、歸檔決策依據。

完整預存程序碼 usp_GetTableSpaceUsage

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'usp_GetTableSpaceUsage' AND type = 'P')
    DROP PROCEDURE usp_GetTableSpaceUsage;
GO

CREATE PROCEDURE usp_GetTableSpaceUsage
AS
BEGIN
    SET NOCOUNT ON;

    -- 結果暫存表
    CREATE TABLE #TableSpace (
        TableName      SYSNAME,
        RowCount       BIGINT,
        ReservedKB     BIGINT,
        DataKB         BIGINT,
        IndexKB        BIGINT,
        UnusedKB       BIGINT
    );

    -- 逐一處理每個使用者資料表
    DECLARE @TableName SYSNAME;
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name;

    OPEN cur;
    FETCH NEXT FROM cur INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- sp_spaceused 回傳格式：name, rows, reserved, data, index_size, unused
        CREATE TABLE #sp_space (
            name       SYSNAME,
            rows       BIGINT,
            reserved   VARCHAR(20),
            data       VARCHAR(20),
            index_size VARCHAR(20),
            unused     VARCHAR(20)
        );

        INSERT #sp_space EXEC sp_spaceused @TableName;

        INSERT #TableSpace
        SELECT 
            @TableName,
            rows,
            CAST(REPLACE(reserved, ' KB', '') AS BIGINT),
            CAST(REPLACE(data, ' KB', '') AS BIGINT),
            CAST(REPLACE(index_size, ' KB', '') AS BIGINT),
            CAST(REPLACE(unused, ' KB', '') AS BIGINT)
        FROM #sp_space;

        DROP TABLE #sp_space;
        FETCH NEXT FROM cur INTO @TableName;
    END

    CLOSE cur;
    DEALLOCATE cur;

    -- 輸出結果 (含 MB 換算與百分比)
    SELECT 
        TableName,
        RowCount,
        ReservedKB,
        CAST(ReservedKB / 1024.0 AS DECIMAL(12,2)) AS ReservedMB,
        DataKB,
        CAST(DataKB / 1024.0 AS DECIMAL(12,2)) AS DataMB,
        IndexKB,
        CAST(IndexKB / 1024.0 AS DECIMAL(12,2)) AS IndexMB,
        UnusedKB,
        CAST(UnusedKB / 1024.0 AS DECIMAL(12,2)) AS UnusedMB,
        CASE WHEN ReservedKB > 0 
             THEN CAST(100.0 * UnusedKB / ReservedKB AS DECIMAL(5,2)) 
             ELSE 0 END AS UnusedPct
    FROM #TableSpace
    ORDER BY ReservedKB DESC;

    DROP TABLE #TableSpace;
END;
GO

執行與結果解讀

EXEC usp_GetTableSpaceUsage;

關鍵指標：

• ReservedMB： 表總保留空間 (資料 + 索引 + 未用)
• UnusedPct > 20%： 疑似碎片嚴重，考慮 DBCC DBREINDEX 或 ALTER INDEX REBUILD
• IndexMB / DataMB > 1.5： 索引過大，檢查重複/未用索引
• RowCount = 0 但 ReservedKB 大： 曾大量刪除未釋放空間，需重建

進階版：整合 sys.dm_db_partition_stats (SQL Server 2005+ 無游標高效版)

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.row_count AS RowCount,
    (p.reserved_page_count * 8) / 1024.0 AS ReservedMB,
    (p.in_row_data_page_count * 8) / 1024.0 AS DataMB,
    ((p.reserved_page_count - p.in_row_data_page_count - p.lob_used_page_count - p.row_overflow_used_page_count) * 8) / 1024.0 AS IndexMB,
    (p.unused_page_count * 8) / 1024.0 AS UnusedMB
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON p.object_id = o.object_id
WHERE o.type = 'U'
ORDER BY ReservedMB DESC;

排程自動化建議

1. 建立 SQL Agent 作業，每日/每週執行
2. 結果寫入管理資料庫歷史表，趨勢分析成長曲線
3. 設定警示：單表成長超過閾值、UnusedPct 持續偏高
4. 配合索引維護計畫 (重建/重組) 自動化

此預存程序為 DBA 日常巡檢、容量規劃、效能調優提供直觀、可量化的資料表空間視圖。