计算出 SQL Server 中一组表的物理大小

如果您希望获取数据库中所有表的大小信息，您可以使用此查询：

SELECT O.type_desc, 
       IndexSize.obj_name,
       I.name AS index_name,
       IndexSize.reserved_MB,
       IndexSize.used_MB,
       IndexSize.row_count,
       IndexSize.object_id,
       IndexSize.index_id
  FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
           SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
           SUM(DDPS.used_page_count)/128.0 AS used_MB,
           SUM(row_count) AS row_count,
           DDPS.object_id,
           DDPS.index_id
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   )IndexSize
   JOIN sys.objects AS O
     ON IndexSize.object_id = O.object_id
   JOIN sys.indexes AS I
     ON IndexSize.object_id = I.object_id
    AND IndexSize.index_id = I.index_id;

它返回保留的兆字节和已使用的兆字节以及数据库中每个索引的行数。第一列告诉您对象类型。这包括 system_tables 以及索引视图。如果您想要一个子集，请在该列上进行过滤。

如果您不需要每个索引的详细信息，您可以改用此查询：

SELECT O.type_desc,
       ObjectSize.obj_name,
       ObjectSize.reserved_MB,
       ObjectSize.used_MB,
       ObjectSize.row_count
  FROM( 
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
           SUM(IndexSize.reserved_MB) AS reserved_MB, 
           SUM(IndexSize.used_MB) AS used_MB, 
           MAX(IndexSize.row_count) AS row_count, 
           IndexSize.object_id
      FROM(
        SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
               SUM(DDPS.used_page_count)/128.0 AS used_MB,
               SUM(row_count) AS row_count,
               DDPS.object_id
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
      )IndexSize
     GROUP BY IndexSize.object_id
  )ObjectSize
  JOIN sys.objects AS O
    ON ObjectSize.object_id = O.object_id

它提供相同的信息，但基于对象。同样，如果您对 system_tables 不感兴趣，请过滤对象类型。

如果您只对普通表感兴趣，您也可以使用“对象资源管理器详细信息”选项卡在 SSMS 中获取信息：

您可以通过在对象资源管理器中选择数据库中的表文件夹然后按 来打开它F7。

您可能必须添加大小列。为此，只需右键单击列列表并选择要查看的列：

您可以使用sp_spaceused存储过程：

例如：

EXECUTE sp_spaceused 'Person.Person' 
EXECUTE sp_spaceused 'Person.Address'

给你这个：

希望这就是您要找的