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]

[v-agajavelly]

Hi @surajpardeshi ,

Thanks for the heads-up. That error is likely due to some table names having spaces, reserved keywords, or special characters (like ICD, 2020, etc.). Let’s switch to a safer version using QUOTENAME() to fully protect the schema and table names, and add error handling so the script doesn’t fail midway.

Here’s the updated query that should work reliably across all your tables.

DECLARE @sql NVARCHAR(MAX) = N'';
-- Build dynamic SQL to count rows in each table
SELECT @sql += 'BEGIN TRY IF (SELECT COUNT(*) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ') = 0 SELECT ' + QUOTENAME(s.name, '''') + '.' + QUOTENAME(t.name, '''') + ' AS table_name, 0 AS row_count; END TRY BEGIN CATCH PRINT ''Error checking table ' + QUOTENAME(s.name, '''') + '.' + QUOTENAME(t.name, '''') + '''; END CATCH; ' FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id;

-- Execute the final SQL 

EXEC sp_executesql @sql;

Regards,
Akhil 

[surajpardeshi]

Hey Akhil, thanks for your help, but unfortunately, even this query failed with multiple errors like below, 

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'app_dre_features_Final_Temp'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'EAI_TRAIN_TGT'.
Msg 102, Level 15, State 1, Line 1

[v-agajavelly]

Hi @surajpardeshi ,

Thanks for the update and yes, you’re absolutely right: sys.dm_pdw_table_distribution_stats is not available in Microsoft Fabric’s modern SQL Warehouse (unlike in Synapse DW or Dedicated Pools).

Since Fabric doesn't yet expose traditional DMVs for row counts, the most reliable workaround is to use dynamic SQL to loop through all user tables and check for zero-row counts. Here's a ready-to-run script:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 
'SELECT ''' + s.name + '.' + t.name + ''' AS table_name, COUNT(*) AS row_count FROM [' + s.name + '].[' + t.name + '] HAVING COUNT(*) = 0 UNION ALL '
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id;
SET @sql = LEFT(@sql, LEN(@sql) - 10); -- remove final UNION ALL
EXEC sp_executesql @sql;

This will return a list of all tables with 0 rows across all schemas.

It’s not as fast as using a DMV, but until Fabric exposes row count metadata (hopefully soon), this method gets the job done safely and reliably.

After following above process, if you still face any error's feel free to get back.

Thanks,
Akhil.

[surajpardeshi]

Hi Akhil, 

I tried the above shared query it ran for 2 minutes and after that it failed with below error -

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '10'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ICD'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2020'.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@CCS_Feature_Type".
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ']'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ']'.

[v-agajavelly]

Hi @surajpardeshi 

Thanks @lbendlin  for your time in addressing the above query, and Thanks @surajpardeshi  for sharing the detailed context, for the thoughtful suggestions earlier.

Since sys.dm_db_partition_stats isn’t supported in Microsoft Fabric Warehouse, the best alternative is to use Fabric-compatible system views like sys.dm_pdw_table_distribution_stats to get the row counts efficiently,  even across thousands of tables.

Here’s the working query to fetch all tables with 0 rows in your Fabric Warehouse.

SELECT
 s.name AS schema_name,
 t.name AS table_name
FROM
 sys.tables t
JOIN
 sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
 sys.dm_pdw_table_distribution_stats p ON t.object_id = p.object_id
GROUP BY
 s.name, t.name
HAVING
 SUM(ISNULL(p.row_count, 0)) = 0
ORDER BY
 schema_name, table_name;

 
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Regards,
Akhil.

[surajpardeshi]

Thanks for your response, I tried running the query shared by you but it failed with the below errors as,  I don't see sys.dm_pdw_table_distribution_stats view present in the System views of my Fabric warehouse. 

Is this view by defualt present in warehouse?

"Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_pdw_table_distribution_stats'.
"

[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.