[T-SQL] 列舉出所有資料表的大小



如果想看看一個資料庫裏面每個表格(TABLE)的大小(使用量)
可以用以下語法
use 你的資料庫名稱
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''


or

use 你的資料庫名稱
SELECT t.schema_name+ ' – '+ t.table_name as schema_table
, t.index_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
, sum(t.tbl_rows) as rows
from
(
SELECT s.Name schema_name
, o.Name table_name
, coalesce(i.Name, 'HEAP') index_name
, p.used_page_count * 8 used
, p.reserved_page_count * 8 reserved
, p.row_count ind_rows
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end tbl_rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o
ON o.object_id = p.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
) as t
GROUP BY
t.schema_name, t.table_name
, t.index_name
ORDER BY
5 desc


See also :
https://social.technet.microsoft.com/Forums/zh-TW/787519c3-ae8c-470d-a1fd-00824f9ba81d/table?forum=sqlservermanagementzhcht

張貼留言

Featured Post

如何查詢AZURE OPEN 到期日/到期點數

* Azure in Open 就跟易付卡點數一樣 兌換完了需要再一年之內用掉 否則歸零 所以並不是省著用就可以用很久 很多人忽略了這點 注意這裡教的是 OPEN點數(儲值信用點數) 如何查詢合約到期日 AZURE EA 與 AZURE CSP等合約方式 不適用 ...

Popular Posts