sqlite count rows of tables identified by subquery

Question

I want to get the count of rows in every table in a Sqlite3 database. I want to avoid writing out a longhand query. I can get the list of tables like this:

SELECT name FROM sqlite_master WHERE type='table'

and I would like to use it in a subquery like this:

select count (*) from (SELECT name FROM sqlite_master WHERE type='table');

but would just return the total rows in the subquery, which isn't what I want.

How can I write a query that will list each table along with their counts?

I have seen dynamic SQL for this kind of thing but I don't think SQLite has that.

I have written a bash loop to do this but I would prefer to do it as a single query

for t in $(sqlite3 data.db "SELECT name FROM sqlite_master WHERE type='table'"); do
  echo -n "$t = "; sqlite3 data.db "SELECT COUNT(*) FROM $t;"
done

Ideas appreciated

Answer 1

I used the following shell syntax to blindly get counts from tables in a database I was debugging.

db="database.db"
for i in `sqlite3 "$db" "SELECT name FROM sqlite_master WHERE 
type='table';"`; do echo -n $i\ \=\ ; sqlite3 "$db" "SELECT 
COUNT(*) FROM $i;"; done

cols = 0
sqlite_sequence = 0
datacols = 17
hits = 0
host = 0
document = 0
admin = 2
comments = 0

Answer 2

If you want to know these values for debugging purposes, look at the output of the sqlite3_analyzer tool.

If you want to use these values in your program, you have to generate the queries dynamically in your program.