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

Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

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

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

以下是通过以下步骤快速获取所有表格大小的方法：

编写给定的T-SQL命令以列出所有数据库表：从INFORMATION_SCHEMA.TABLES中选择“exec sp_spaceused”+TABLE_NAME，其中TABLE_TYPE=“BASE TABLE”现在复制数据库表列表，并将其复制到新的查询分析器窗口中执行sp_spaceused表1执行sp_spaceused表2执行sp_spaceused表3执行sp_spaceused表4执行sp_spaceused表5在SQL查询分析器中，从顶部工具栏中选择“结果到文件”选项（Ctrl+Shift+F）。现在，最后点击上面工具栏中标记为红色的Execute按钮。所有表的数据库大小现在存储在计算机上的文件中。

我要感谢Greg Low先生的提问：

SELECT o.name AS ObjectName, 
       SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB 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
    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 

下面是一个示例查询，用于获取按大小降序排列的大于1GB的表。

USE YourDB
GO

DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs

; WITH CTE AS
(
SELECT
    i.object_id,
    Rows = MAX(p.rows),
    TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
    UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM 
    sys.indexes i
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
WHERE
    i.object_id > 255
GROUP BY
    i.object_id
HAVING
    SUM(a.total_pages) * @Mult > 1
)
SELECT 
    SchemaName = s.name,
    TableName = t.name,
    c.TotalSpaceGB,
    c.UsedSpaceGB,
    UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
    [RowCount] = c.Rows
FROM 
    CTE c
JOIN    
    sys.tables t ON t.object_id = c.object_id
JOIN
    sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
    c.TotalSpaceGB DESC