扩展到@xav答案，处理表分区以获得MB和GB大小。在SQL Server 2008/2012上测试（注释了一行，其中is_memory_optized=1）

SELECT
    a2.name AS TableName,
    a1.rows as [RowCount],
    --(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
    --a1.data * 8 AS DataSize_KB,
    --(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
    --(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
    --'| |' Separator_MB_GB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
    CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
        --===Remove the following comment for SQL Server 2014+
        --WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable'       --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC

与Marc_s的回答有一点不同，因为我经常回到这一页，按大多数第一行排序：

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    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
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    --p.rows DESC --Uncomment to order by amount rows instead of size in KB.
    SUM(a.total_pages) DESC 

如果需要精确计算SSMS中“表财产-存储”页面上的相同数字，则需要使用与SSMS中相同的方法进行计数（适用于sql server 2005及更高版本……也适用于具有LOB字段的表，因为仅计算“used_pages”不足以显示准确的索引大小）：

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

当处理多个分区和/或筛选索引时，Marc_s的答案给出了错误的结果。它也不区分数据和索引的大小，这通常是非常相关的。一些建议的修复方法并不能解决核心问题，或者根本就是错误的。

以下查询解决了所有这些问题。

SELECT 
     [object_id]        = t.[object_id]
    ,[schema_name]      = s.[name]
    ,[table_name]       = t.[name]
    ,[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
    ,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END
    ,[index_type]       = i.[type_desc]
    ,[partition_count]  = p.partition_count
    ,[row_count]        = p.[rows]
    ,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
                               ELSE (  SELECT DISTINCT p.data_compression_desc
                                       FROM sys.partitions p
                                       WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
                                    )
                          END
    ,[total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables  t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
    SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
    FROM sys.partitions
    GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
    SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
    GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

我发现这个查询很容易使用和快速。

select schema_name(tab.schema_id) + '.' + tab.name as [table], 
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables (nolock) tab
inner join sys.indexes (nolock) ind 
    on tab.object_id = ind.object_id
inner join sys.partitions  (nolock) part 
    on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units (nolock) spc
    on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc

扩展到@xav答案，处理表分区以获得MB和GB大小。在SQL Server 2008/2012上测试（注释了一行，其中is_memory_optized=1）

SELECT
    a2.name AS TableName,
    a1.rows as [RowCount],
    --(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
    --a1.data * 8 AS DataSize_KB,
    --(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
    --(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
    --'| |' Separator_MB_GB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
    CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
        --===Remove the following comment for SQL Server 2014+
        --WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable'       --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC