发布：2023/8/2 20:07:31作者：管理员 来源：本站 浏览次数：719
要查看 SQL Server 中哪个表占用的空间最多，您可以使用以下查询来列出所有表及其占用的空间大小，并按照占用空间从大到小进行排序：
SELECT
t.NAME AS TableName,
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.NAME, p.Rows
ORDER BY
TotalSpaceKB DESC;
此查询将返回一个表，其中包含了每个非系统表的名称、行数、总空间、已使用空间以及未使用空间的大小。结果将按照总空间从大到小进行排序，因此第一行将是占用空间最大的表。
请注意，这个查询只考虑了表所占用的空间，并不考虑表中的索引、视图、触发器、存储过程等其他对象所占用的空间。如果您想要查看所有对象的空间占用情况，可以使用其他系统视图和函数进行查询。
如果您想以 MB 为单位查看表占用的空间，可以将查询中的所有 * 8 操作替换为 * 8 / 1024，这样可以将空间从字节转换为千字节（KB），然后再除以 1024 转换为兆字节（MB），如下所示：
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.NAME, p.Rows
ORDER BY
TotalSpaceMB DESC;
这样，结果将会以 MB 为单位显示表的总空间、已使用空间和未使用空间的大小。
当然可以，您可以使用 AS 关键字来为查询中的列指定别名，如下所示：
SELECT
t.NAME AS 表名,
p.rows AS 行数,
SUM(a.total_pages) * 8 / 1024 AS 总空间MB,
SUM(a.used_pages) * 8 / 1024 AS 已用空间MB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS 未用空间MB
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.NAME, p.Rows
ORDER BY
总空间MB DESC;
© Copyright 2014 - 2024 柏港建站平台 ejk5.com. 渝ICP备16000791号-4