Forum Discussion
Helper IVFabric Warehouse Table Size
Hi there,
I have 100s of tables in fabric warehouse. How do i find out table sizes of each tables? Any query?
online suggestion -
SELECT
s.name AS schema_name,
t.name AS table_name,
SUM(ps.used_page_count) * 8 / 1024.0 AS size_mb,
SUM(ps.reserved_page_count) * 8 / 1024.0 AS reserved_mb
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
JOIN sys.dm_db_partition_stats ps
ON t.object_id = ps.object_id
GROUP BY
s.name,
t.name
ORDER BY
size_mb DESC;select * from (
select obj.name, (sum(reserved_page_count) * 8.0)/1024 as "size in MB",
((sum(reserved_page_count) * 8.0)/1024)/1024 as "size in GB"
from sys.dm_db_partition_stats part, sys.objects obj where part.object_id = obj.object_id group by obj.name
)a
DMV (Dynamic Management View) 'dm_db_partition_stats' is not supported.
Hi AJAJ ,
This is expected behavior in Fabric Warehouse and you are not doing anything wrong. The query you found online depends on sys.dm_db_partition_stats, which is a SQL Server DMV and is not supported in Fabric Warehouse. Fabric uses a distributed, serverless SQL engine and only exposes a limited set of system views, so detailed storage allocation information is not available through T SQL. Because of this design, there is currently no supported SQL query that can return the exact size of each table in MB or GB from inside the warehouse.
If you need accurate table storage information, the supported approach is to use the Capacity Metrics app in the Fabric workspace, which shows the actual compressed storage used by warehouse tables. From within SQL, you can still retrieve row counts and column metadata to get a general sense of which tables are larger or smaller, but this should be treated as an estimate only and may not reflect true storage usage due to compression and internal optimizations. In short, the DMV error you are seeing is expected, and using Fabric Capacity Metrics is the correct way to view table sizes today.
Thank you.
3 Replies
v-tejramaCommunity Support
Hi AJAJ ,
This is expected behavior in Fabric Warehouse and you are not doing anything wrong. The query you found online depends on sys.dm_db_partition_stats, which is a SQL Server DMV and is not supported in Fabric Warehouse. Fabric uses a distributed, serverless SQL engine and only exposes a limited set of system views, so detailed storage allocation information is not available through T SQL. Because of this design, there is currently no supported SQL query that can return the exact size of each table in MB or GB from inside the warehouse.
If you need accurate table storage information, the supported approach is to use the Capacity Metrics app in the Fabric workspace, which shows the actual compressed storage used by warehouse tables. From within SQL, you can still retrieve row counts and column metadata to get a general sense of which tables are larger or smaller, but this should be treated as an estimate only and may not reflect true storage usage due to compression and internal optimizations. In short, the DMV error you are seeing is expected, and using Fabric Capacity Metrics is the correct way to view table sizes today.
Thank you.