获取数据库中所有表的大小

技术背景

在数据库管理中，了解数据库中各个表的大小是非常重要的。它有助于进行数据库性能优化、空间规划和资源分配。不同的数据库系统有不同的方法来获取表的大小信息，本文主要介绍在 SQL Server 中获取所有表大小的多种方法。

实现步骤

1. 使用 SQL 查询语句

可以通过编写 SQL 查询语句来获取所有表的大小信息。例如：

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) 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
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 
    TotalSpaceMB DESC, t.name

2. 使用 SQL Server Management Studio (SSMS) 的标准报告

如果使用 SSMS，可以通过以下步骤获取表的大小信息：

1. 右键单击数据库。
2. 导航到 Reports > Standard Reports > Disk Usage By Table。

3. 使用系统存储过程 sp_spaceused

可以使用 sp_spaceused 存储过程来获取单个表或整个数据库的磁盘使用信息。例如：

USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO

要一次性获取所有表的信息，可以使用以下语句：

USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO

核心代码

查询所有表大小的 SQL 语句

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) 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
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 
    TotalSpaceMB DESC, t.name

使用 sp_spaceused 获取所有表信息

USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO

最佳实践

• 定期监控：定期运行查询来监控表的大小变化，以便及时发现异常增长或空间浪费的情况。
• 过滤系统表：在查询中排除系统表（如 t.is_ms_shipped = 0），以避免获取不必要的信息。
• 排序和筛选：根据需要对结果进行排序和筛选，例如按表大小降序排序或筛选出大于特定大小的表。

常见问题

1. 查询结果包含重复行

如果使用查询返回了重复行，可以尝试使用标准报告功能（如 SSMS 中的 Disk Usage By Table 报告）。

2. 分区表和过滤索引的问题

某些查询在处理分区表和过滤索引时可能会给出不正确的结果。可以使用以下查询来解决这些问题：

SELECT 
    [object_id]        = t.[object_id],
    [schema_name]      = s.[name],
    [table_name]       = t.[name],
    [index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END,
    [object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END,
    [index_type]       = i.[type_desc],
    [partition_count]  = p.partition_count,
    [row_count]        = p.[rows],
    [data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
                               ELSE (  SELECT DISTINCT p.data_compression_desc
                                       FROM sys.partitions p
                                       WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
                                    )
                          END,
    [total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2)),
    [used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2)),
    [unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables  t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
    SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
    FROM sys.partitions
    GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
    SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
    GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

3. 数据库兼容性问题

使用 SSMS 的标准报告时，数据库兼容性级别必须设置为 90 或以上才能正常工作。可以参考 相关文档 进行设置。