SQL Server 快速取得所有使用者表空間佔用：sp_MSforeachtable + sp_spaceused 批次統計

前言

DBA 常需快速掌握全庫各表大小、列數、索引大小、未使用空間，以規劃歸檔、分割、壓縮策略。本文提供一支儲存程序，利用未文件化但廣泛使用的 sp_MSforeachtable 批次呼叫 sp_spaceused，結果寫入暫存表供即時查詢。

完整程式碼

CREATE PROCEDURE dbo.sp_get_table_space_info
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 建立結果暫存表 (若不存在)
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'TableSpaceInfo')
    BEGIN
        CREATE TABLE TableSpaceInfo (
            name        VARCHAR(255),   -- 表名
            rows        BIGINT,         -- 列數
            reserved    VARCHAR(20),    -- 總保留空間 (含索引)
            data        VARCHAR(20),    -- 資料空間
            index_size  VARCHAR(20),    -- 索引空間
            unused      VARCHAR(20)     -- 未使用空間
        );
    END

    -- 2. 清空舊資料
    DELETE FROM TableSpaceInfo;

    -- 3. 批次執行 sp_spaceused
    EXEC sp_MSforeachtable @command1 = "INSERT INTO TableSpaceInfo EXEC sp_spaceused '?'";

    -- 4. 完成，直接查詢 TableSpaceInfo 即可
    -- SELECT * FROM TableSpaceInfo ORDER BY CAST(REPLACE(reserved, ' KB', '') AS BIGINT) DESC;
END
GO

使用方式

-- 1. 執行一次統計
EXEC sp_get_table_space_info;

-- 2. 查詢結果 (依總空間降冪)
SELECT
    name,
    rows,
    reserved,
    data,
    index_size,
    unused
FROM TableSpaceInfo
ORDER BY CAST(REPLACE(reserved, ' KB', '') AS BIGINT) DESC;

-- 3. 進階分析範例
-- 前 10 大表
SELECT TOP 10 * FROM TableSpaceInfo ORDER BY CAST(REPLACE(reserved, ' KB', '') AS BIGINT) DESC;

-- 索引佔比超過 50% 的表 (可能需壓縮/重建)
SELECT name, index_size, data,
       CAST(REPLACE(index_size, ' KB', '') AS FLOAT) / NULLIF(CAST(REPLACE(data, ' KB', '') AS FLOAT), 0) * 100 AS idx_data_ratio_pct
FROM TableSpaceInfo
WHERE CAST(REPLACE(data, ' KB', '') AS BIGINT) > 0
  AND CAST(REPLACE(index_size, ' KB', '') AS FLOAT) / CAST(REPLACE(data, ' KB', '') AS FLOAT) > 0.5
ORDER BY idx_data_ratio_pct DESC;

-- 空表 (rows=0 但保留空間>0) 清理候選
SELECT * FROM TableSpaceInfo WHERE rows = 0 AND CAST(REPLACE(reserved, ' KB', '') AS BIGINT) > 0;

取得整個資料庫大小

EXEC sp_spaceused;  -- 不帶參數 = 目前資料庫總覽

注意事項與替代方案

DMV 版本 (推薦用於自動化監控)

SELECT
    OBJECT_NAME(ps.object_id) AS TableName,
    SUM(ps.row_count) AS RowCount,
    SUM(ps.in_row_reserved_page_count) * 8.0 / 1024 AS Reserved_MB,
    SUM(ps.in_row_used_page_count) * 8.0 / 1024 AS Data_MB,
    (SUM(ps.in_row_reserved_page_count) - SUM(ps.in_row_used_page_count)) * 8.0 / 1024 AS Unused_MB
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t ON ps.object_id = t.object_id
WHERE t.type = 'U'
GROUP BY ps.object_id
ORDER BY Reserved_MB DESC;

結語

sp_MSforeachtable + sp_spaceused 是快速取得表級空間佔用的經典組合，適合臨時分析、一次性報表。自動化監控、大型資料庫、高頻需求請改用 sys.dm_db_partition_stats DMV，效能差異可達 100 倍以上。建議兩套腳本並存：DMV 做日常巡檢，sp_spaceused 做深度驗證。