[SOLVED] SQL Query to calculate size of each schemas in Azure data warehouse and to find last access time?

SQL Query to calculate size of each schemas in Azure data warehouse and to find last access time?

Is there any SQL query that could help me get the size of each schemas present in our Azure Data Warehouse service and also to find when was it last accessed?

I checked and found one query below which can help me find the size of each table in schema but considering I have 15K+ tables its not feasible.

-- Check space used by a specific table
DBCC PDW_SHOWSPACEUSED('Demo.Analysis');

Solution

This is the view is adjusted to use sys.pdw_permanent_table_mappings as per the Synapse recommendation.

The Dynamic management views queries allow you to get a report of all table sizes.

For exmple I have created the below:

SCHEMA Sales
SCHEMA HR



SELECT two_part_name, SUM( row_count ) AS row_count, SUM( reserved_space_GB ) AS reserved_space_GB
FROM dbo.vTableSizes
GROUP BY two_part_name
ORDER BY reserved_space_GB DESC;

Results:

two_part_name   row_count   reserved_space_GB
[HR].[Departments]  2   0.004464
[HR].[Employees]    2   0.004464
[Sales].[Customers] 2   0.004464
[Sales].[Orders]    2   0.004464