一條指令掌握所有資料表資訊：sp_MSForEachTable 進階應用

需快速取得資料庫中所有表的名稱、欄位數、列數、空間使用量？善用未公開預存程序 sp_MSForEachTable，單一批次完成。

完整指令碼

EXEC sp_MSForEachTable
    @precommand = N'
        CREATE TABLE ##TblInfo (
            ID INT IDENTITY,
            表名 SYSNAME,
            欄位數 INT,
            列數 INT,
            保留空間 NVARCHAR(10),
            使用空間 VARCHAR(10),
            索引空間 VARCHAR(10),
            未用空間 VARCHAR(10)
        )',
    @command1 = N'
        INSERT INTO ##TblInfo (表名, 列數, 保留空間, 使用空間, 索引空間, 未用空間)
        EXEC sp_spaceused ''?'';
        UPDATE ##TblInfo SET 欄位數 = (
            SELECT COUNT(*) FROM syscolumns WHERE id = OBJECT_ID(''?''))
        WHERE ID = SCOPE_IDENTITY()',
    @postcommand = N'SELECT * FROM ##TblInfo ORDER BY ID; DROP TABLE ##TblInfo';

輸出範例

原理解析

• sp_MSForEachTable 遍歷使用者資料表，將 ? 替換為表名（含 Schema）。
• @precommand 建立暫存表 ##TblInfo（全域暫存表，跨批次可見）。
• @command1 對每表執行 sp_spaceused 取得空間資訊，再查 syscolumns 統計欄位數。
• @postcommand 輸出結果並清理暫存表。

注意事項

• sp_MSForEachTable 為未公開/不支援程序，未來版本可能移除或行為變更，生產環境關鍵流程建議改寫為游標或動態 SQL。
• SQL 2005+ 可改用 sys.dm_db_partition_stats + sys.columns 系統檢視達成同樣功能，官方支援。
• 大量資料表（> 5,000）執行時間較長，建議離峰執行。

官方支援替代查詢（SQL 2005+）

SELECT
    t.name AS 表名,
    COUNT(c.column_id) AS 欄位數,
    p.rows AS 列數,
    CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(10,2)) AS 保留空間_MB,
    CAST(SUM(a.used_pages) * 8. / 1024 AS DECIMAL(10,2)) AS 使用空間_MB,
    CAST(SUM(CASE WHEN i.index_id IN (0,1) THEN a.used_pages ELSE 0 END) * 8. / 1024 AS DECIMAL(10,2)) AS 索引空間_MB,
    CAST(SUM(a.total_pages - a.used_pages) * 8. / 1024 AS DECIMAL(10,2)) AS 未用空間_MB
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
LEFT JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.is_ms_shipped = 0
GROUP BY t.name, p.rows
ORDER BY 保留空間_MB DESC;

掌握這招，資料庫容量規劃、索引優化、歸檔決策都能一目瞭然。