标签归档:表空间占用

mssql sqlserver 查询当前数据库下所有表的占用空间的方法分享


摘要:
下文将分享sql脚本查询数据库下所有表空间的信息,如下所示:
实验环境:sql server 2008 R2


select 
name '数据表名称', row_Count as '表行数',
(reservedpages * 8) '表空间已用(K)',
(pages * 8)  '表数据占用空间(K)',
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '表所涉及索引占用空间(K)',
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '表未用空间(K)',
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' G') as '表已用空间(G)'
from(
SELECT name,
SUM (reserved_page_count) as reservedpages ,
SUM (used_page_count) as usedpages ,
SUM (
     CASE
        WHEN (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,
SUM (
   CASE
    WHEN (index_id < 2) THEN row_count
    ELSE 0
    END
   )  as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type='U'
group by sys.objects.name ) t
order by '表空间已用(K)' desc

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_表占用空间统计