MS SQL資料庫裡資料表的使用空間(初估)資訊

–資料庫裡資料表的使用空間(初估)資訊
–將gloqas改成執行的資料庫名稱即可
USE gloqas Go
SELECT a3.name AS N’結構描述’, a2.name AS N’資料表’, a1.rows AS N’資料列筆數’,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS N’已保留(KB)’, a1.data * 8 AS ‘資料使用空間(KB)’,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 AS N’索引(KB)’,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8 AS N’未使用(KB)’
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 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 sys.dm_db_partition_stats ps INNER JOIN 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 sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N’S’ and a2.type <> N’IT’
ORDER BY 3 DESC
GO

本篇發表於 IT 並標籤為 。將永久鏈結加入書籤。

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

你可以使用這些 HTML 標籤與屬性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

(若看不到驗證碼,請重新整理網頁。)