System.Data.SQLite: Check SQLite database is not empty (has at least one row in any table)

Question

I need to check with System.Data.SQLite library that my SQLite database is not empty (has at least one row in any table). For now I get table names from sqlite_master table and then query each table with SELECT * FROM [TableName] LIMIT 1.

But I search for a more optimized approach. Is it possible with a single .ExecuteQuery<T>() call?

Answer 1

Interesting question. As far as I know there's no simple single query you can run that will do this, but you can use the ANALYZE command and the resultant sqlite_stat1 table to figure it out.

When you run ANALYZE as a non-query statement, it creates and fills the sqlite_stat1 table with some data including the row count for the table. The trick that will help solve your question is this: tables with zero rows are not listed in the output.

This gives us an option for a single-line solution that runs two statements in SQLite to give the answer you're after:

bool IsEmpty = db.SqlQuery<int>($"ANALYZE;SELECT COUNT(*) FROM sqlite_stat1").ToArray().Single() == 0;

While not pretty, and it does affect the database state, it's at least simple.

Of course the ANALYZE command does a scan of the database, so if there's a lot of data and/or indices it'll take some time.