Check if any database table has any rows

Question

I am trying to understand how to check if any table in my db has data using entity framework. I can check for one table but how can I check for all tables at once? DO we have any option with ef6?

using (var db = new CreateDbContext())
{
    if(!db.FirstTable.Any())
    {
        // The table is empty
    }
}

Any pointers on how to loop through entities would be helpful.

Answer 1

Here is one way you could do this with t-sql. This should be lightning fast on most systems. This returned in less than a second on our ERP database. It stated 421 billion rows in more than 15,000 partition stats.

select sum(p.row_count)
from sys.dm_db_partition_stats p
join sys.objects o on o.object_id = p.object_id
where o.type not in ('S', 'IT') --excludes system and internal tables.

Answer 2

Similar to @SeanLange, but shows schema name and table name for tables without any rows.

SELECT  Distinct OBJECT_SCHEMA_NAME(p.object_id) AS [Schema], 
        OBJECT_NAME(p.object_id) AS [Table]
FROM    sys.partitions p
        INNER JOIN sys.indexes i 
            ON p.object_id = i.object_id
            AND p.index_id = i.index_id
WHERE   OBJECT_SCHEMA_NAME(p.object_id) != 'sys'
        And p.Rows = 0    
ORDER BY [Schema], [Table]