SQL Server 統計所有使用者表大小：參考 sp_MSforeachdb 的儲存程式碼

查詢單一資料庫所有使用者表尺寸

SELECT @@SERVERNAME AS ServerName,
       DB_NAME() AS DBName,
       object_id AS ObjectID,
       SCHEMA_NAME(schema_id) AS SchName,
       name AS TableName,
       Rowcnt AS Rows,
       Columns,
       Indexes,
       RowLength,
       ReservedKb,
       TableUsedKb,
       UsedKb - TableUsedKb AS IndexUsedKb,
       ReservedKb - UsedKb AS UnusedKb,
       create_date AS CreateDate,
       modify_date AS LastModifiedDate,
       GETUTCDATE() AS TrackingUTCTime
FROM (
    SELECT
        t.object_id,
        t.schema_id,
        t.name,
        (SELECT MAX(row_count) FROM sys.dm_db_partition_stats p WITH (NOLOCK)
         WHERE p.object_id = t.object_id AND p.index_id < 2) AS Rowcnt,
        (SELECT COUNT(1) FROM sys.syscolumns WITH (NOLOCK) WHERE id = t.object_id) AS Columns,
        (SELECT COUNT(DISTINCT index_id) FROM sys.dm_db_partition_stats p WITH (NOLOCK)
         WHERE p.object_id = t.object_id) AS Indexes,
        (SELECT SUM(length) FROM sys.syscolumns WITH (NOLOCK) WHERE id = t.object_id) AS RowLength,
        ISNULL((SELECT SUM(reserved_page_count) FROM sys.dm_db_partition_stats p WITH (NOLOCK)
                WHERE p.object_id = t.object_id), 0) * 8
        + ISNULL((SELECT SUM(reserved_page_count)
                  FROM sys.dm_db_partition_stats p2 WITH (NOLOCK)
                  INNER JOIN sys.internal_tables it WITH (NOLOCK) ON p2.object_id = it.object_id
                  WHERE it.parent_id = t.object_id
                    AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)), 0) * 8 AS ReservedKb,
        ISNULL((SELECT SUM(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                FROM sys.dm_db_partition_stats p WITH (NOLOCK)
                WHERE p.object_id = t.object_id AND p.index_id < 2), 0) * 8 AS TableUsedKb,
        ISNULL((SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats p WITH (NOLOCK)
                WHERE p.object_id = t.object_id), 0) * 8
        + ISNULL((SELECT SUM(used_page_count)
                  FROM sys.dm_db_partition_stats p2 WITH (NOLOCK)
                  INNER JOIN sys.internal_tables it WITH (NOLOCK) ON p2.object_id = it.object_id
                  WHERE it.parent_id = t.object_id
                    AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)), 0) * 8 AS UsedKb,
        t.create_date,
        t.modify_date
    FROM sys.tables t WITH (NOLOCK)
    WHERE t.type = 'U'
) A
ORDER BY ReservedKb DESC;

欄位含義

擴充：遍歷所有使用者資料庫

搭配未文件化預存程序 sp_MSforeachdb：

EXEC sp_MSforeachdb @command1 = '
    USE [?];
    IF DB_ID() > 4 -- 排除 system DB
    BEGIN
        -- 將上述查詢放入這裡，或插入暫存表彙總
    END
';

注意：sp_MSforeachdb 參數 @command1 長度限制 NVARCHAR(2000)，複雜查詢建議寫成預存程序再呼叫，或改用 sys.databases 游標迴圈更可控。

定期執行與趨勢分析

1. 建立永久資料表 dbo.TableSizeHistory 存放每日快照。
2. SQL Agent 排程每日執行，寫入 TrackingUTCTime。
3. 配合報表工具 (Power BI / SSRS) 繪製成長趨勢圖，預測磁碟擴充時機。

效能提示

• 使用 WITH (NOLOCK) 避免封鎖，接受髒讀風險（統計用途可接受）。
• 大量資料庫建議分批次執行，避免長時間佔用 tempdb。
• SQL 2016+ 可改用 sys.dm_db_partition_stats 配合 sys.tables 更簡潔。