MSSQL查询Database内所有数据表的笔数与大小

来源:邁向程式殿堂  发布日期:2013-12-04 19:17:06
今天突然遇到老板想要知道自己系统数据库所使用的大小,基本上直接到硬盘空间看这个Database的档案大小就可以,不过,当然这样做的意义并不大,要管数据库大小主要是针对一些大型数据表进行数据切割动作,一方面降低硬盘耗用,另一方面也可以加速IO在查询数据速度。
 
不过,一时也忘记要怎样去写,好险之前有上过百敬老师的课,发现残留的程序代码还在,赶紧来这边备注一下

SELECT *,
round(convert(numeric(25,10),RESERVED)/convert(numeric(25,10),1024),4) AS USED_MB,
round(convert(numeric(25,10),RESERVED)/convert(numeric(25,10),1024*1024),4) AS USED_GB,
case when TABLE_ROWS <> 0
 then round(
 (1024.000000*convert(numeric(25,10),RESERVED))/
 convert(numeric(25,10),TABLE_ROWS),5)
 else null
end AS AVERAGE_BYTES_PER_ROW,
case when TABLE_ROWS <> 0
 then round(
 (1024.000000*convert(numeric(25,10),DATA))/
 convert(numeric(25,10),TABLE_ROWS),5)
 else null
end AS AVERAGE_DATA_BYTES_PER_ROW,
case when TABLE_ROWS <> 0
 then round(
 (1024.000000*convert(numeric(25,10),INDEX_SIZE))/
 convert(numeric(25,10),TABLE_ROWS),5)
 else null
end AS AVERAGE_INDEX_BYTES_PER_ROW,
case when TABLE_ROWS <> 0
 then round(
 (1024.000000*convert(numeric(25,10),UNUSED))/
 convert(numeric(25,10),TABLE_ROWS),5)
 else null
end AS AVERAGE_UNUSED_BYTES_PER_ROW
FROM
(SELECT
 a3.name AS [schemaname],
 a2.name AS [Table_name],
 a1.rows as Table_rows,
 (a1.reserved + ISNULL(a4.reserved,0))* 8 AS Reserved,
 a1.data * 8 AS Data,
 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
 THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS Index_size,
 (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
 THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS Unused
FROM
 (SELECT ps.object_id,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN row_count ELSE 0
   END
   ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN
    (ps.in_row_data_page_count +
    ps.lob_used_page_count + ps.row_overflow_used_page_count)
    ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
   END
   ) AS data,
  SUM (ps.used_page_count) AS used
 FROM AdventureWorks2012.sys.dm_db_partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
 (SELECT
  it.parent_id,
  SUM(ps.reserved_page_count) AS reserved,
  SUM(ps.used_page_count) AS used
  FROM AdventureWorks2012.sys.dm_db_partition_stats ps
  INNER JOIN AdventureWorks2012.sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN AdventureWorks2012.sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN AdventureWorks2012.sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
) Basic
ORDER BY Reserved DESC


 

上面程序代码是采用AdventureWorks2012资料库为范例,透过数据库的sys.XXX数据表取的该数据库内所有数据表的信息,
 
若是要换一个数据库查询时只要将AdventureWorks2012换为另一个数据库名字就可以,查询结果如下
 

 
通常我们会比较在意的数据为红框地方的数据,往往可以先由这些信息判断是否有进行数据表的搬移或是是否有不必要的数据还
 
必须留在这边

 

Tag标签: Database  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规