浮雲雅築: [研究] [SQL] 顯示資料庫中所有資料表(table)大小(SQL 2016)

2017年2月20日星期一

[研究] [SQL] 顯示資料庫中所有資料表(table)大小(SQL 2016)

[研究] [SQL] 顯示資料庫中所有資料表(table)大小(SQL 2016)

2017-02-20

SELECT
sys.tables.NAME AS TableName,
sys.schemas.Name AS SchemaName,
sys.partitions.rows AS RowCounts,
SUM(sys.allocation_units.total_pages) * 8 AS TotalSpaceKB,
SUM(sys.allocation_units.total_pages) * 8 /1024 AS TotalSpaceMB,
SUM(sys.allocation_units.used_pages) * 8 AS UsedSpaceKB,
(SUM(sys.allocation_units.total_pages) - SUM(sys.allocation_units.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables
INNER JOIN
sys.indexes ON sys.tables.OBJECT_ID = sys.indexes.object_id
INNER JOIN
sys.partitions ON sys.indexes.object_id = sys.partitions.OBJECT_ID AND sys.indexes.index_id = sys.partitions.index_id
INNER JOIN
sys.allocation_units ON sys.partitions.partition_id = sys.allocation_units.container_id
LEFT OUTER JOIN
sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
WHERE
sys.tables.NAME NOT LIKE 'dt%'
AND sys.tables.is_ms_shipped = 0
AND sys.indexes.OBJECT_ID > 255
GROUP BY
sys.tables.Name, sys.schemas.Name, sys.partitions.Rows
ORDER BY
UsedSpaceKB desc ,sys.tables.Name

或

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceKB desc ,t.Name

(完)

系統檢視 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms177862.aspx

sys.allocation_units (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189792.aspx#

How scripts retrieve information about our database objects
http://www.databasezone.com/techdocs/dbscriptshow.html