如果您只关心数据库中的空浪费空间，而不关心单个表，则可以考虑以下问题：

如果数据库经历了大量的数据插入和删除，可能与ETL情况类似，这将导致数据库中有太多未使用的空间，因为文件组会自动增长，但不会自动收缩。

您可以通过使用数据库的财产页面来查看是否是这种情况。您可以收缩（右键单击数据库>任务>收缩）并收回一些空间。但是，如果根本原因仍然存在，则数据库将增长（并花费额外的时间尝试增长，直到增长到足够的速度，所以不要这样做）

当处理多个分区和/或筛选索引时，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

如果您使用的是SQL Server Management Studio（SSMS），则可以运行标准报告，而不是运行查询（在我的情况下返回重复的行）

右键单击数据库导航到报告>标准报告>磁盘使用情况（按表）

注意：数据库兼容级别必须设置为90或更高，才能正常工作。看见http://msdn.microsoft.com/en-gb/library/bb510680.aspx

下面是一个示例查询，用于获取按大小降序排列的大于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

我在marc_s答案的顶部添加了几列：

with fs
as
(
select i.object_id,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKb
from     sys.indexes i 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 
    i.OBJECT_ID > 255 
GROUP BY 
    i.object_id,
    p.rows
)

SELECT 
    t.NAME AS TableName,
    fs.RowCounts,
    fs.TotalSpaceKb,
    t.create_date,
    t.modify_date,
    ( select COUNT(1)
        from sys.columns c 
        where c.object_id = t.object_id ) TotalColumns    
FROM 
    sys.tables t INNER JOIN      
    fs  ON t.OBJECT_ID = fs.object_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
ORDER BY 
    t.Name

经过一番搜索，我找不到一种简单的方法来获取所有表格的信息。有一个名为sp_spaceused的方便存储过程，它将返回数据库使用的所有空间。如果提供了表名，则返回该表使用的空间。但是，存储过程返回的结果不可排序，因为列是字符值。

以下脚本将生成我要查找的信息。

create table #TableSize (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #ConvertedSizes (
    Name varchar(255),
    [rows] int,
    reservedKb int,
    dataKb int,
    reservedIndexSize int,
    reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows], 
SUBSTRING(reserved, 0, LEN(reserved)-2), 
SUBSTRING(data, 0, LEN(data)-2), 
SUBSTRING(index_size, 0, LEN(index_size)-2), 
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
order by reservedKb desc

drop table #TableSize
drop table #ConvertedSizes