如何查看数据库中所有表所占存储的大小

[woshiyanliu]

假设我有一个数据库，里面有300表，如何通过一个sql语句或者其他什么操作查看我这300张表里面每个表所占存储的大小

[天-笑]

with    t as (SELECT    o.name,
                        SUM(p.reserved_page_count * 8 / 1024) as reserved_size_MB,
                        SUM(p.used_page_count * 8 / 1024) as used_size_MB,
                        SUM(CASE WHEN (p.index_id < 2)
                                 THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)
                                 ELSE p.lob_used_page_count + p.row_overflow_used_page_count
                            END) * 8 / 1024 as Data_size_MB,
                        SUM(CASE WHEN (p.index_id < 2) THEN row_count
                                 ELSE 0
                            END) as rowCounts
              FROM      sys.dm_db_partition_stats p
              inner join sys.objects o on p.object_id = o.object_id
              group by  o.name
             )
     select name, reserved_size_MB, used_size_MB,
            (used_size_MB - Data_size_MB) as Index_size_MB, rowCounts
     from   t
     order by reserved_size_MB desc

• 打赏
• 举报

回复 赞

[Haytor]

参考一下我的这个方案

DECLARE @Table TABLE(
name NVARCHAR(32),
rows INT,
reserved NVARCHAR(32),
data  NVARCHAR(32),
index_size  NVARCHAR(32),
unused  NVARCHAR(32)
)


INSERT @Table
EXEC SP_MSFOREACHTABLE 'exec sp_spaceused ''?'''


SELECT
	*
FROM 
	@Table 
--WHERE name IN ('表名')

• 打赏
• 举报

回复 赞