查詢 SQL Server 每個資料表空間大小的三種實用方法

掌握資料表空間佔用情況是容量規劃、效能調校與歸檔決策的基礎。本文介紹三種查詢所有資料表大小的方法，從簡易單行指令到完整報表腳本，滿足不同場景需求。

核心系統預存程序：sp_spaceused

sp_spaceused 可回傳指定資料表或整個資料庫的列數、保留空間、資料空間、索引空間與未使用空間。

-- 單一資料表
EXEC sp_spaceused N'dbo.Orders';

-- 整個資料庫（含更新使用量統計）
EXEC sp_spaceused @updateusage = N'TRUE';

方法一：sp_MSforeachtable 快速列出所有資料表大小（非文件化、不支援但廣泛使用）

EXEC sp_MSforeachtable N'EXEC sp_spaceused ''?''';

• 優點：一行指令、無需建立物件、即時回傳多個結果集。
• 缺點：輸出格式為多個獨立結果集，難以排序、篩選、匯出；非官方文件化功能，未來版本可能移除。

方法二：游標迴圈建立暫存表產生完整排序報表（穩健、可客製化）

-- 1. 建立結果暫存表
IF OBJECT_ID('tempdb..#TableSpaceInfo') IS NOT NULL DROP TABLE #TableSpaceInfo;
CREATE TABLE #TableSpaceInfo (
    TableName      SYSNAME,
    RowCount       BIGINT,
    ReservedKB     BIGINT,
    DataKB         BIGINT,
    IndexSizeKB    BIGINT,
    UnusedKB       BIGINT
);

-- 2. 宣告游標遍歷使用者資料表
DECLARE @TableName SYSNAME;
DECLARE @SQL NVARCHAR(MAX);
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name)
    FROM sys.tables
    WHERE is_ms_shipped = 0
    ORDER BY name;

OPEN Cur;
FETCH NEXT FROM Cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'INSERT #TableSpaceInfo (TableName, RowCount, ReservedKB, DataKB, IndexSizeKB, UnusedKB) ' +
               N'EXEC sp_spaceused @objname = ' + @TableName + N', @updateusage = N''TRUE'';';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM Cur INTO @TableName;
END
CLOSE Cur; DEALLOCATE Cur;

-- 3. 輸出排序報表（按保留空間降序）
SELECT 
    TableName,
    RowCount,
    CAST(ReservedKB / 1024.0 AS DECIMAL(12,2)) AS ReservedMB,
    CAST(DataKB / 1024.0 AS DECIMAL(12,2))     AS DataMB,
    CAST(IndexSizeKB / 1024.0 AS DECIMAL(12,2)) AS IndexMB,
    CAST(UnusedKB / 1024.0 AS DECIMAL(12,2))    AS UnusedMB
FROM #TableSpaceInfo
ORDER BY ReservedKB DESC;

方法三：sys.dm_db_partition_stats 動態管理檢視（高效能、集合式查詢，SQL 2005+）

SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) + N'.' + OBJECT_NAME(p.object_id) AS TableName,
    SUM(p.row_count)                                          AS RowCount,
    CAST(SUM(p.reserved_page_count) * 8.0 / 1024 AS DECIMAL(12,2)) AS ReservedMB,
    CAST(SUM(p.used_page_count) * 8.0 / 1024 AS DECIMAL(12,2))     AS UsedMB,
    CAST((SUM(p.reserved_page_count) - SUM(p.used_page_count)) * 8.0 / 1024 AS DECIMAL(12,2)) AS UnusedMB
FROM sys.dm_db_partition_stats p
JOIN sys.tables t ON t.object_id = p.object_id
WHERE t.is_ms_shipped = 0
  AND p.index_id IN (0,1) -- 堆積或叢集索引
GROUP BY p.object_id
ORDER BY ReservedMB DESC;

• 優點：單一查詢、無游標開銷、效能極佳、可直接 JOIN 其他 DMV 擴充欄位（如資料壓縮狀態、分割資訊）。
• 適用版本：SQL Server 2005 及以上。

進階：含索引細項的完整空間報表

SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    p.partition_number,
    p.row_count,
    CAST(p.reserved_page_count * 8.0 / 1024 AS DECIMAL(12,2)) AS ReservedMB,
    CAST(p.used_page_count * 8.0 / 1024 AS DECIMAL(12,2))     AS UsedMB
FROM sys.indexes i
JOIN sys.dm_db_partition_stats p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.tables t ON t.object_id = i.object_id
WHERE t.is_ms_shipped = 0
ORDER BY TableName, IndexName;

選擇建議

定期執行空間檢查、設定閾值警示，能有效避免磁碟空間耗盡導致的服務中斷。