A query that returns the size of a table in a SQLite database

Question

I have a SQLite database that contains several tables. We are writing a maintenance tool that removes "obsolete" data from a table until the size of this table is determined as a percentage of the total database file or less.

I know how to determine the size of a database file - I do this by doing

PRAGMA PAGE_SIZE;

and

PRAGMA PAGE_COUNT;

in two separate queries and multiplying these two to get the file size in bytes. Yes, I know that I can just take the file size in bytes, but this is similar to how I did it in other databases, and I want to stick with it, at least for now.

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

Answer 1

There is no easy way to query the size of a table. So what I finished doing, it came up with an estimate of the size of the table, multiplying the number of rows by an estimate of the row 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), as well as an estimate of the average length of the columns of the row using the query. Something like that:

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:

• It will overestimate the size of any row with an integer or long column, which may be null, and this is null
• It will overestimate or underestimate the length of the row columns.
• It does not take into account the size of the indexes.

. , , .

Answer 2

sqlite3_analyzer, . , , SQLite:

$ sqlite3_analyzer my_db.sqlite

Answer 3

SQLite SQLITE_ENABLE_DBSTAT_VTAB, dbstat. table TABLENAME:

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

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

, CLA sqlite3_anazlyer, .