Find the number of rows in each table in a large SQLite database

Question

I have a SQLite database with a large number of tables. I would like to find out which have zero rows (and drop them). I have not got very far! What I can do is list the tables using this SQL:

SELECT tbl_name FROM sqlite_master WHERE type = 'table'

Is there a way in SQLite of turning that into a loop and printing the COUNT(*) for each of the tables?

(N.B. I have looked at Recursive Common Table Expressions (CTEs), but I cannot see how they might help.)

Answer 1

Is there a way in SQLite of turning that into a loop and printing the COUNT(*) for each of the tables?

Thanks to the magic of sqlite3, the answer is yes if we allow that sqlite3 is part of SQLite.

Anyway, the technique comes in very handy.

Assuming $db is the database file of interest:

echo "SELECT 'SELECT count(*), \"' || name || '\" FROM ' || name || ';'  FROM sqlite_master WHERE type = 'table';" |
    sqlite3 -readonly "$db" | sqlite3 -readonly "$db"

For the famous flights.db database, this produces:

8107|airports
6048|airlines
67663|routes
1|sqlite_stat1
24|sqlite_stat4

Season to taste.

Answer 2

First number in stat column of sqlite_stat1 is the number of rows. So, if you have write privileges, then:

ANALYZE main;
select * from sqlite_stat1

If the database is never changing in size, you can just run the second command any time after running ANALYZE just once. But you'll have to rerun ANALYZE if the database is frequently changed.