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

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?

Solution

There's no easy way to query the size of a table. So what I ended up doing is coming up with an estimate of the table's size by multiplying the number of rows by an estimate of the row's size. I manually summed the length of the integer columns (4 bytes each), plus the sum of the length of the long columns (8 bytes each), plus an estimate of the average length of the string columns using a query. Something like this:

SELECT COUNT(*) *  -- The number of rows in the table
     ( 24 +        -- The length of all 4 byte int columns
       12 +        -- The length of all 8 byte int columns
       128 )       -- The estimate of the average length of all string columns
FROM MyTable

The only problems with this are that:

It will overestimate the size of any row with an integer or long column that can be null, and that happens to be null
It will overestimate or underestimate the length of the string columns.
It does not take into account the size of the indexes.

This was good enough for our implementation. You might be able to do a better job computing the table's size with a more complicated query that takes nulls & the actual length of the string columns into account.