sql查询数据库每张表存储

最新推荐文章于 2025-02-15 19:56:50 发布

qq_26490863

最新推荐文章于 2025-02-15 19:56:50 发布

阅读量407

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_26490863/article/details/83070199

本文介绍了一种使用SQL查询来分析数据库中各表及其索引占用空间的方法，通过创建CTE（公用表表达式）来计算每个表的大小（包括数据和索引），并展示如何将这些数据转换为更易于理解的MB单位。

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

;with cte as (
SELECT
t.name as chexian,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.chexian,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc