查詢資料庫中依資料表占的容量大小排序

 USE databaseName; -- 替換為你的資料庫名稱

GO

WITH TableSizes AS

(

    SELECT 

        t.NAME AS TableName,

        p.rows AS RowCounts,

        SUM(a.total_pages) * 8 AS TotalSpaceKB,

        SUM(a.used_pages) * 8 AS UsedSpaceKB,

        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

    FROM 

        sys.tables t

    INNER JOIN      

        sys.indexes i ON t.OBJECT_ID = i.object_id

    INNER JOIN 

        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

    INNER JOIN 

        sys.allocation_units a ON p.partition_id = a.container_id

    WHERE 

        t.NAME NOT LIKE 'dt%' AND

        i.OBJECT_ID > 255 AND   

        i.index_id <= 1

    GROUP BY 

        t.Name, p.Rows

)

SELECT 

    TableName,

    RowCounts,

    TotalSpaceKB,

    UsedSpaceKB,

    UnusedSpaceKB

FROM 

    TableSizes

ORDER BY 

    TotalSpaceKB DESC;