在上面的@Mark回答中，添加了@updateusage='true'以强制更新最新的尺码统计(https://msdn.microsoft.com/en-us/library/ms188776.aspx):

        SET NOCOUNT ON
        DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
        DECLARE @cmd1 varchar(500)
        SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '

        INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
        EXEC sp_msforeachtable @command1=@cmd1 
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC

从使用OSQL的命令提示符：

OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt

要获取一个数据库中的所有表大小，可以使用以下查询：

Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

您可以将其更改为将所有结果插入临时表，然后从临时表中进行选择。

Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" ' 
Select * from #TempTable

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 
    TotalSpaceMB DESC, t.Name

以上查询有助于查找表（包括索引）使用的空间量，但如果要比较表上的索引使用了多少空间，请使用以下查询：

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
    sys.indexes AS i
    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
    i.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id

如果需要精确计算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