统计各表记录数和空间占用情况

SELECT s.Name AS SchemaName,
t.NAME AS TableName,
SUM(p.rows)/COUNT(distinct a.type) AS RowCounts,
CAST(SUM(a.total_pages) as float) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
SUM(a.data_pages) * 8 AS DataSpaceKB,
CAST((SUM(a.total_pages) - SUM(a.used_pages)) as float) * 8 AS UnusedSpaceKB,
MIN(FILEGROUP_NAME(a.data_space_id)) as FileGroup
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.index_id < 2
GROUP BY t.Name, s.Name
ORDER BY TotalSpaceKB desc

Posted in 数据库 at January 7, 2018. by 傻猫 .    Views: 228    No Comments