sqlserver表占用空间大小排序

最新推荐文章于 2025-05-27 15:00:00 发布

原创最新推荐文章于 2025-05-27 15:00:00 发布 · 838 阅读

1 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种SQL查询方法，用于分析数据库中各表的空间使用情况，包括总空间、已用空间和未用空间，以KB和MB为单位进行详细展示。通过连接多个系统表，如sys.tables、sys.indexes、sys.partitions和sys.allocation_units，来获取精确的数据表空间信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 SELECT 
      t.NAME AS TableName,
      s.Name AS SchemaName,
      p.rows AS RowCounts,
      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 1=1
     AND t.is_ms_shipped = 0
     AND i.OBJECT_ID > 255 
 GROUP BY 
     t.Name, s.Name, p.Rows
 ORDER BY 
     t.Name