sp_spaceused可以获取表、索引视图或整个数据库使用的磁盘空间的信息。

例如：

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

这将报告ContactInfo表的磁盘使用情况信息。

要同时对所有表使用此选项：

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

您还可以从SQL Server的右键单击“标准报告”功能中获取磁盘使用情况。要获取此报告，请从对象资源管理器中的服务器对象导航，向下移动到数据库对象，然后右键单击任何数据库。从出现的菜单中，选择“报告”，然后选择“标准报告”，再选择“磁盘分区使用情况：[DatabaseName]”。

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

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

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

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

Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

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

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

-- Show the size of all the tables in a database sort by data size descending
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 ''?'''

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

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

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