1

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.

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
Kurkula
  • 6,386
  • 27
  • 127
  • 202
  • 1
    You would have to loop through all tables and check them one at a time. – Sean Lange Jun 28 '18 at 19:46
  • Thanks for pointer. Just checking if there is any build in method in entityframework which does that to decrease time and improve performance. – Kurkula Jun 28 '18 at 19:47
  • 1
    I don't know EF but I doubt anything like that exists because it couldn't actually help with performance. The sql engine has to query for each table. – Sean Lange Jun 28 '18 at 19:55
  • 1
    If that's something you needs, it would be best to create trigger that updates a table that contains that information. That way you have 1 request to see if any table has data. – Erik Philips Jun 28 '18 at 20:26
  • 1
    @SeanLange any way to update statistics and use this? Got to be better than if exists or something ya? – S3S Jun 28 '18 at 20:27
  • 1
    @ErikPhilips you would have to add that trigger to every single table in the database. And then it would just firing all the time which seems to be a huge waste of resources. My guess is they are just trying to see if the database is a fresh install or not. – Sean Lange Jun 28 '18 at 20:38
  • 1
    @scsimon yes I think the query I posted should work, and it is way faster than a loop looking at each table or something along those lines. – Sean Lange Jun 28 '18 at 20:39
  • 1
    Nice @SeanLange i was looking around for something like this. – S3S Jun 28 '18 at 20:41

2 Answers2

5

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.
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
3

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]
George Mastros
  • 24,112
  • 4
  • 51
  • 59