概要
查询MSSQL数据库中最大单表的大小。
SQL语句
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
SUM(p.rows) AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
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
GROUP BY
t.NAME, s.Name
ORDER BY
SUM(a.total_pages) DESC;
小结
提示:这里可以添加总结
这将返回数据库中最大单表的名称、模式名称、行数以及总空间、已用空间和未使用空间的大小(以KB为单位)。