sqlite query to get all list of table names with number of records in it

Question

Please help on below query : sqlite query to get all list of table names with number of records in it :

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.

Since table names have to be known when the query is being prepared, you have to do it in steps. Get a list of table names and loop over that running one query per table, inserting the table name into a query string each time. — Shawn
yes, getting the data in loop is one way . i was looking for single query that will the resulted data . If some inner joins can be done on "sqlite_master" — Bhushan Muttha

DinoCoderSaurus DinoCoderSaurus · Accepted Answer · 2019-05-27T12:34:25

Perhaps you use the results of ANALYZE to create a workaround. It creates the internal schema object sqlite_stat1

2.6.3. The sqlite_stat1 table

The sqlite_stat1 is an internal table created by the ANALYZE command and used to hold supplemental information about tables and indexes that the query planner can use to help it find better ways of performing queries. Applications can update, delete from, insert into or drop the sqlite_stat1 table, but may not create or alter the sqlite_stat1 table. The schema of the sqlite_stat1 table is as follows:
CREATE TABLE sqlite_stat1(tbl,idx,stat);
There is normally one row per index, with the index identified by the name in the sqlite_stat1.idx column. The sqlite_stat1.tbl column is the name of the table to which the index belongs. In each such row, the sqlite_stat.stat column will be a string consisting of a list of integers followed by zero or more arguments. The first integer in this list is the approximate number of rows in the index. (The number of rows in the index is the same as the number of rows in the table, except for partial indexes.) .....

If there are no partial indexes, the SELECT tbl,cast(stat as INT) will return the number of rows in each table, unless the table has 0 rows.

This sql gives the expected results on a small (25MB, 34 tables, 26 indexes, 33K+ rows) production database. Your mileage may (will?) vary.

ANALYZE;
select  DISTINCT tbl_name, CASE WHEN stat is null then 0 else cast(stat as INT) END numrows 
from sqlite_master m 
LEFT JOIN sqlite_stat1 stat on   m.tbl_name = stat.tbl 
where m.type='table'
and m.tbl_name not like 'sqlite_%'
order by 1;
--drop table sqlite_stat1;

sqlite query to get all list of table names with number of records in it

1 Answers