Query that returns the size of a table in a SQLite database

Question

I have a SQLite database that contains a number of tables. We are writing a maintenance tool that will remove "stale" data from a table until that table's size is a certain percentage of the total database file or smaller.

I know how to determine the size of the database file -- I do it by executing

PRAGMA PAGE_SIZE; 

and

PRAGMA PAGE_COUNT; 

in two separate queries and multiplying the two to get the file size in bytes. Yes, I know I can just take the size of the file in bytes, but this is similar to the way I've done it in other databases and I want to stick with it, at least for now.

My problem is I don't know how to get the size of a TABLE. There has to be some way to do this. Can anyone point me in the right direction?

Answer 1

If SQLite is compiled with SQLITE_ENABLE_DBSTAT_VTAB, you can query the dbstat table. This should return the table size (in bytes) of table TABLENAME:

SELECT SUM("pgsize") FROM "dbstat" WHERE name='TABLENAME'; 

https://www.sqlite.org/dbstat.html

This is what is used by the sqlite3_anazlyer CLI tool, which can also be used for this purpose.