也许表格有更多的分区文件，必须显示文件顺序

SELECT
  T1.Name                                       AS TableName,
  T5.Name                                       AS SchemaName,
  T3.partition_number                           AS PartionNumber,   
  T3.Rows                                       AS RowsCount,
  SUM(T4.total_pages) * 8                       AS TotalSpaceKB,
  SUM(T4.used_pages) * 8                        AS UsedSpaceKB,
  (SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
  sys.objects T1  INNER JOIN 
  sys.indexes T2 ON T1.object_id = T2.object_id  INNER JOIN 
  sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id  INNER JOIN
  sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
  sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
  T1.type='U'
GROUP BY
  T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
  T1.Name,T3.partition_number;

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

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

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

我的文章只与SQL Server 2000相关，并且已经过测试，可以在我的环境中工作。

该代码访问单个实例的所有可能的数据库，而不仅仅是单个数据库。

我使用两个临时表来帮助收集适当的数据，然后将结果转储到一个“Live”表中。

返回的数据是：DatabaseName、DatabaseTableName、Rows（在表中）、data（表的大小，以KB为单位）、entry data（我发现这对了解我上次运行脚本的时间很有用）。

这段代码的缺点是“data”字段不是以int形式存储的（字符“KB”保留在该字段中），这对于排序是有用的（但不是完全必要的）。

希望这段代码能帮助一些人，并为他们节省一些时间！

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]

AS
BEGIN
   SET NOCOUNT OFF

   CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
   CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

   DECLARE @SQL nvarchar(4000)
   SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '

   INSERT INTO #DatabaseTables(DbName, TableName)
      EXECUTE sp_msforeachdb @Command1=@SQL

   DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR   
   SELECT TableName FROM #DatabaseTables

   DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR   
   SELECT DBName FROM #DatabaseTables

   DECLARE @DBName sysname  
   OPEN AllDatabaseNames  

   DECLARE @TName sysname
   OPEN AllDatabaseTables  

   WHILE 1=1 BEGIN 
      FETCH NEXT FROM AllDatabaseNames INTO @DBName  
      FETCH NEXT FROM AllDatabaseTables INTO @TName 
      IF @@FETCH_STATUS<>0 BREAK  
      INSERT INTO #AllDatabaseTableSizes
         EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName) 

   END 

   --http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
   INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
      SELECT   [dbname], name, [rows],  data FROM #DatabaseTables
      INNER JOIN #AllDatabaseTableSizes
      ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
      GROUP BY [dbname] , name, [rows],  data
      ORDER BY [dbname]
   --To be honest, I have no idea what exact duplicates we are dropping
    -- but in my case a near enough approach has been good enough.
   DELETE FROM [rsp_DatabaseTableSizes]
   WHERE name IN 
      ( 
      SELECT name 
      FROM [rsp_DatabaseTableSizes]
      GROUP BY name
      HAVING COUNT(*) > 1
      )

   DROP TABLE #DatabaseTables
   DROP TABLE #AllDatabaseTableSizes

   CLOSE AllDatabaseTables  
   DEALLOCATE AllDatabaseTables  

   CLOSE AllDatabaseNames  
   DEALLOCATE AllDatabaseNames      
END

--EXEC [dbo].[usp_getAllDBTableSizes] 

如果您需要知道，rsp_DatabaseTableSizes表是通过以下方式创建的：

CREATE TABLE [dbo].[rsp_DatabaseSizes](
    [DatabaseName] [varchar](1000) NULL,
    [dbSize] [decimal](15, 2) NULL,
    [DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]

GO

CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed 
(
     TableName sysname
    ,NumRows BIGINT
    ,ReservedSpace VARCHAR(50)
    ,DataSpace VARCHAR(50)
    ,IndexSize VARCHAR(50)
    ,UnusedSpace VARCHAR(50)
) 

insert into #tmp_table_info
select s.name+'.'+t.name 
from sys.tables t 
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';

declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
    declare @tablename varchar(200)
    select @tablename=tblname
    from #tmp_table_info
    where id =@min

    DECLARE @str VARCHAR(500)
    SET @str =  'sp_spaceused '''+@tablename+''''
    INSERT INTO #SpaceUsed 
    EXEC (@str)  
    set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed

作为marc_s答案（已被接受的答案）的一个简单扩展，它被调整为返回列计数并允许过滤：

SELECT *
FROM
(

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
    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
INNER JOIN
    INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
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
) AS Result

WHERE
    RowCounts > 1000
    AND ColumnCount > 10
ORDER BY 
    UsedSpaceKB DESC

经过一番搜索，我找不到一种简单的方法来获取所有表格的信息。有一个名为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