SQL Query To find list of tables with 0 Row Counts in MS Fabric Warehouse

[surajpardeshi]

I want to find out list of table and schema's present in my Microsoft Fabric Warehouse which have 0 row count or basically which are empty. I have around 9000+ tables. The following queries work fine in our Azure Data Warehouse but don't work in the Fabric Warehouse.

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

I tried running the below query as well using UNION ALL, but for 9K tables, it would take a lot of time. Can anyone please suggest the right query for Fabric Warehouse to get the list of tables with 0 Row Counts?

SELECT 'AAT.ANA1' AS table_name, COUNT(*) AS row_count FROM [AAT].[ANA1]
UNION ALL
SELECT 'AAT.ANA2' AS table_name, COUNT(*) AS row_count FROM [AAT].[ANA2]
UNION ALL
SELECT 'AAT.ANA3' AS table_name, COUNT(*) AS row_count FROM [AAT].[ANA3]

[lbendlin]

Use 

select * from sys.tables

and then iterate over the result.

This query 

SELECT t.name       AS table_name
       ,s.row_count AS row_count
FROM   sys.tables t
JOIN   sys.dm_db_partition_stats s
  ON t.OBJECT_ID = s.OBJECT_ID
 AND t.type_desc = 'USER_TABLE'
 AND t.name NOT LIKE '%dss%' --Exclude tables created by SQL Data Sync for Azure.
 AND s.index_id IN (0, 1)
ORDER  BY table_name;
select * from sys.dm_db_partition_stats

unfortunately fails because allegedly DMV (Dynamic Management View) 'dm_db_partition_stats' is not supported.