locked
how to find the rowcounts of all the heap tables in Azure data warehouse. RRS feed

  • Question

  • how to find the rowcounts of all the heap tables in Azure data warehouse.

    I have the below query to find the rowcounts of Clustered column store tables but this is  not working for heap tables. The reason beiogn these tables does not have entry on  sys.pdw_nodes_tables  and sys.dm_pdw_nodes_db_column_store_row_group_physical_stats

    Could some one help me with a query to find the row counts of all the heap tables in a Azure Datawarehouse database.

    select sm.name, tb.name FROM sys.schemas sm
    INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
    INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
    INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
    INNER JOIN sys.dm_pdw_nodes_db_column_store_row_group_physical_stats rg
    ON rg.object_id = nt.object_id
    AND rg.pdw_node_id = nt.pdw_node_id
    AND rg.distribution_id = nt.distribution_id
    WHERE 1 = 1
    GROUP BY sm.name, tb.name
    ORDER BY SUM(rg.total_rows) DESC

    Wednesday, March 13, 2019 10:05 PM

All replies

  • Hi,

    Thanks for your feedback. I am checking internally to find the command for this.

    Thanks for your patience. I'll get back soon with some update on this.

    Thursday, March 14, 2019 1:33 PM
  • Hi,

    Thanks for your patience. For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket, else could you send an email to AzCommunity@Microsoft.com with the below details, we would like to work closer with you on this matter. 

    Thread URL:
    Subscription ID:

    • Proposed as answer by angoyal-msft Tuesday, March 19, 2019 6:17 AM
    Tuesday, March 19, 2019 6:17 AM
  • Hi,

    If an approximation is enough then you can use the sys.partitions view to check the row counts of all tables.

    Thursday, March 21, 2019 1:22 PM