SQL Server 查詢所有資料表大小與空間佔用的高效腳本

腳本用途

快速掌握資料庫中每張使用者資料表的列數、保留空間、資料空間、索引空間及未使用空間（KB），按保留空間降序排列，便於定位大表優化。

完整 T-SQL 腳本

SET NOCOUNT ON;

-- 暫存表收集各表空間統計
CREATE TABLE #spt_space (
    objid    INT NULL,
    rows     INT NULL,
    reserved DEC(15) NULL,
    data     DEC(15) NULL,
    indexp   DEC(15) NULL,
    unused   DEC(15) NULL
);

DECLARE @id INT;
DECLARE c_tables CURSOR FOR
    SELECT id FROM sysobjects WHERE xtype = 'U';  -- 僅使用者資料表

OPEN c_tables;
FETCH NEXT FROM c_tables INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 保留空間
    INSERT INTO #spt_space (objid, reserved)
    SELECT @id, SUM(reserved)
    FROM sysindexes
    WHERE indid IN (0, 1, 255) AND id = @id;

    -- 資料頁數
    DECLARE @pages INT;
    SELECT @pages = SUM(dpages)
    FROM sysindexes
    WHERE indid < 2 AND id = @id;

    SELECT @pages = @pages + ISNULL(SUM(used), 0)
    FROM sysindexes
    WHERE indid = 255 AND id = @id;

    UPDATE #spt_space SET data = @pages WHERE objid = @id;

    -- 索引空間
    UPDATE #spt_space
    SET indexp = (SELECT SUM(used) FROM sysindexes WHERE indid IN (0,1,255) AND id = @id) - data
    WHERE objid = @id;

    -- 未使用空間
    UPDATE #spt_space
    SET unused = reserved - (SELECT SUM(used) FROM sysindexes WHERE indid IN (0,1,255) AND id = @id)
    WHERE objid = @id;

    -- 列數
    UPDATE #spt_space SET [rows] = i.[rows]
    FROM sysindexes i
    WHERE i.indid < 2 AND i.id = @id AND objid = @id;

    FETCH NEXT FROM c_tables INTO @id;
END

CLOSE c_tables;
DEALLOCATE c_tables;

-- 最終輸出，換算為 KB
SELECT 
    TableName = LEFT(o.name, 60),
    [Rows] = CONVERT(CHAR(11), s.rows),
    ReservedKB = LTRIM(STR(s.reserved * d.low / 1024., 15, 0)) + ' KB',
    DataKB     = LTRIM(STR(s.data * d.low / 1024., 15, 0)) + ' KB',
    IndexSizeKB = LTRIM(STR(s.indexp * d.low / 1024., 15, 0)) + ' KB',
    UnusedKB   = LTRIM(STR(s.unused * d.low / 1024., 15, 0)) + ' KB'
FROM #spt_space s
JOIN master.dbo.spt_values d ON d.number = 1 AND d.type = 'E'
JOIN sysobjects o ON o.id = s.objid
ORDER BY s.reserved DESC;

DROP TABLE #spt_space;

關鍵說明

• 使用 sysindexes 系統舊表相容 SQL Server 2000~2019，現代版本建議改用 sys.dm_db_partition_stats。
• 游標逐表統計，適合中小型資料庫；超大數據庫建議改用集合式查詢或 sp_spaceused 批次處理。
• 結果欄位已轉換為 KB 單位並附單位字串，方便直接匯出報表。

現代化改寫範例 (SQL Server 2012+)

SELECT 
    t.name AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS ReservedKB,
    SUM(a.used_pages) * 8 AS DataKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.object_id > 255
GROUP BY t.name, p.rows
ORDER BY ReservedKB DESC;

結語

定期執行空間統計腳本，配合索引重組、資料歸檔策略，能有效控管資料庫成長，避免儲存危機。