如何获取近6个月内所有数据库表的UsedSpaceMB数据

需求：获取数据库中所有表近6个月数据的占用空间（UsedSpaceMB）。

当前使用的查询语句返回的是表创建以来的总空间大小：

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

自定义尝试的查询仍返回总空间大小，未达到需求：

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
    t.modify_date AS ModifyDate
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.modify_date >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
    AND t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows, t.modify_date
ORDER BY 
    TotalSpaceMB DESC, t.Name, t.modify_date;

问题原因 #

你使用t.modify_date过滤的是表结构的修改时间，不是数据的更新/插入时间，因此无法筛选出近6个月数据的空间。同时，sys.allocation_units等系统视图统计的是整个表的物理空间，无法直接按数据时间范围拆分空间占用。

解决方案 #

根据表的存储方式，分两种情况处理：

1. 表包含数据时间字段（如CreateTime、UpdateTime） #

如果表中有记录数据插入/更新时间的字段，可以通过统计近6个月数据的行数占比，估算对应的空间占用（该方法为近似值，因为每行数据大小可能不一致）：

WITH TableTotalSpace AS (
    -- 先统计各表的总空间和总行数
    SELECT 
        t.OBJECT_ID,
        t.NAME AS TableName,
        s.Name AS SchemaName,
        SUM(p.rows) AS TotalRows,
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalUsedSpaceMB
    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.OBJECT_ID, t.Name, s.Name
),
RecentDataRows AS (
    -- 统计各表近6个月的数据行数（需替换为实际时间字段名，比如CreateTime）
    SELECT 
        t.OBJECT_ID,
        t.NAME AS TableName,
        s.Name AS SchemaName,
        COUNT(*) AS RecentRows
    FROM 
        sys.tables t
    LEFT JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    CROSS APPLY (
        SELECT * FROM [s.Name].[t.Name] 
        WHERE CreateTime >= DATEADD(MONTH, -6, GETDATE()) -- 替换为你的时间字段
    ) AS RecentData
    WHERE 
        t.NAME NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
    GROUP BY 
        t.OBJECT_ID, t.Name, s.Name
)
SELECT 
    tts.TableName,
    tts.SchemaName,
    rd.RecentRows AS Recent6MonthsRows,
    tts.TotalRows,
    CAST(ROUND((rd.RecentRows * 1.0 / tts.TotalRows) * tts.TotalUsedSpaceMB, 2) AS NUMERIC(36, 2)) AS Recent6MonthsUsedSpaceMB,
    tts.TotalUsedSpaceMB
FROM 
    TableTotalSpace tts
LEFT JOIN 
    RecentDataRows rd ON tts.OBJECT_ID = rd.OBJECT_ID
ORDER BY 
    Recent6MonthsUsedSpaceMB DESC, tts.TableName;

注意：将代码中的CreateTime替换为表实际的时间字段名。如果表没有时间字段，此方法无法使用。

2. 表按时间分区 #

如果表是按时间分区（比如每月一个分区），可以直接查询对应分区的空间占用：

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    pf.name AS PartitionFunction,
    ps.name AS PartitionScheme,
    p.partition_number,
    prv.value AS PartitionBoundary,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
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
INNER JOIN 
    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN 
    sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_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
    -- 过滤近6个月的分区边界（需根据分区字段类型调整，这里假设是日期类型）
    AND prv.value >= DATEADD(MONTH, -6, GETDATE())
GROUP BY 
    t.Name, s.Name, pf.name, ps.name, p.partition_number, prv.value
ORDER BY 
    UsedSpaceMB DESC, t.Name;

注意：需根据你的分区字段和边界调整prv.value的过滤条件。

内容的提问来源于stack exchange，提问作者Abdul Rehman