mssql sqlserver 如何查询一个数据库中 表,索引 的空间占用情况


摘要:
下文分享通过sql脚本查询一个数据库中的表 索引的占用情况及表行数,如下所示:
实验环境:sqlserver 2008 R2


  ---通过  sys.dm_db_partition_stats系统表获取用户数据表的占用情况
  SELECT o.name ,
[已用空间] = str(SUM (reserved_page_count)*8,15,0)+' KB',  
[数据占用空间]= str(SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)*8,15,0) + ' KB',
[索引占用空间]=str(case when sum(used_page_count)>SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END) then 
     sum(used_page_count)-SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)
      else 0 end*8,15,0)+' KB',
[表行数] = SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END),
[未使用空间]=str(case when SUM (reserved_page_count)>SUM (used_page_count) then SUM (reserved_page_count)-SUM (used_page_count) else 0 end*8,15,0)+' KB'
FROM sys.dm_db_partition_stats p join sys.sysobjects o on p.object_id=o.id and o.xtype='U'
group by o.name 
 order by SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) desc
mssql_sqlserver_表占用空间统计

mssql_sqlserver_表占用空间统计