Using sys.partitions to find rows of tables

[MikkelBS]

Hey,

I'm trying to use sys.partitions to get the number of rows for all tables, such as described here. I'm in Fabric, and using the SQL Analytics Endpoint for a Lakehouse, and just a SQL query in Warehouse.

However, for all tables except system tables (e.g., sysowners), the partition_number column equals 1, but rows is zero.

Is this a limitation with Lakehouse/Warehouse, am I misunderstanding the purpose of sys.partitions, or is there something wrong going on?

This is one of the queries I've tried:

select t.name, p.partition_number, p.rows, o.name 
from sys.partitions as p
left join sys.tables as t
    on t.object_id = p.object_id 
left join sys.objects as o
    on p.object_id = o.object_id 

Regards,

[nilendraFabric · Accepted solution]

Hi @MikkelBS 

Using sys.partitions to get row counts in Microsoft Fabric has limitations that you're encountering. In Fabric (both Lakehouse SQL Analytics Endpoints and Warehouses), the rows column in sys.partitions typically returns 0 for user tables, despite the partition_number showing as 1

https://sqlkover.com/get-row-counts-of-all-tables-in-a-microsoft-fabric-warehouse/

The most reliable method to get row counts at the moment is to use SELECT COUNT(1) on each table.

[MikkelBS]

Thank you, @nilendraFabric - interesting link as well, I didn't know the rows column only gave an approximate row count, for instance.

If I understand you correctly, the intention behind the rows column is to show the number of rows, but is currently limited to only do so for system tables. Do you have any idea whether this limitation will be removed at some point in the future, so that we can also use it for user tables?

Regards,

Mikkel

[V-yubandi-msft]

Hi @MikkelBS ,
Thank you for engaging with the Microsoft Fabric community, @nilendraFabric ,Your response is much appreciated.

In Microsoft Fabric, the rows column in sys.partitions doesn't display row counts for user tables, typically returning 0, while partition_number is 1. This is a known limitation.

To obtain row counts, the most reliable method is to use SELECT COUNT(1) for each table.

Microsoft has not provided any updates on whether this limitation will be resolved.  To help prioritize this, you can submit feedback or vote for related ideas in the Microsoft Fabric Ideas Forum.
Fabric Ideas - Microsoft Fabric Community

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[MikkelBS]

Thank you, @V-yubandi-msft 😊