要查看 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;
标签:SUM,NAME,占用,id,sys,Sql,空间,server,pages From: https://www.cnblogs.com/LuoCore/p/17279123.html