求Azure数据仓库各Schema大小及最后访问时间的SQL查询

一、查询各Schema总占用空间 #

无需逐个表执行DBCC PDW_SHOWSPACEUSED，可以通过系统视图聚合计算Schema级别的空间使用情况：

-- 计算各Schema的总占用空间（单位：GB）
SELECT 
    s.name AS schema_name,
    ROUND(SUM(dsu.used_page_count * 8.0 / 1024 / 1024), 2) AS total_space_gb
FROM 
    sys.schemas s
INNER JOIN sys.tables t 
    ON s.schema_id = t.schema_id
INNER JOIN sys.pdw_table_mappings tm 
    ON t.object_id = tm.object_id
INNER JOIN sys.pdw_nodes_tables nt 
    ON tm.physical_name = nt.name
INNER JOIN sys.dm_pdw_db_space_usage dsu 
    ON nt.object_id = dsu.object_id
GROUP BY 
    s.name
ORDER BY 
    total_space_gb DESC;

这个查询通过关联sys.schemas、sys.tables和Azure Synapse特有的空间使用视图sys.dm_pdw_db_space_usage，一次性统计所有Schema的总占用空间，效率远高于单表查询。

二、查询各Schema最后访问时间 #

Schema的最后访问时间需要基于其包含的所有表的最新访问记录聚合而来，可通过索引使用统计视图实现：

-- 获取各Schema的最后访问时间（取Schema内表的最新访问操作时间）
SELECT 
    s.name AS schema_name,
    MAX(access_time) AS last_access_time
FROM 
    sys.schemas s
INNER JOIN sys.tables t 
    ON s.schema_id = t.schema_id
LEFT JOIN (
    -- 合并表的各类访问操作时间
    SELECT 
        object_id,
        MAX(last_user_seek) AS access_time
    FROM sys.dm_db_index_usage_stats
    GROUP BY object_id
    UNION ALL
    SELECT 
        object_id,
        MAX(last_user_scan) AS access_time
    FROM sys.dm_db_index_usage_stats
    GROUP BY object_id
    UNION ALL
    SELECT 
        object_id,
        MAX(last_user_lookup) AS access_time
    FROM sys.dm_db_index_usage_stats
    GROUP BY object_id
) table_access 
    ON t.object_id = table_access.object_id
GROUP BY 
    s.name
ORDER BY 
    last_access_time DESC;

注意事项：

• sys.dm_db_index_usage_stats的统计数据会在服务重启或索引重建后重置，若需长期跟踪访问时间，建议建立自定义日志表定期记录这些数据。
• 若Schema内所有表从未被访问过，last_access_time会返回NULL。

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