SQLite Forum

Table size
Login

Table size

(1) By ayagmur75 on 2021-02-18 12:00:34 [link] [source]

I want to calculate table size. I have already tried the following options, but I could not get a satisfactory result.

select sum ("pgsize") from dbstat where name="TBNAME"; --> a slow query in my environment

select sum(length(HEX(blob_value))) from TBNAME; --> a slow query in my environment

select count(*) * estimated_row_size from TBNAME; --> very good, but it is based on estimation.


select page_count * page_size from pragma_page_count(), pragma_page_size(); --> if some records are deleted, it gives wrong results.

Can you give me a suggestion?

Thank you for you in advance,
Best Regards,

(2) By Richard Hipp (drh) on 2021-02-18 12:43:11 in reply to 1 [link] [source]

I think those are your options.

(3) By Keith Medcalf (kmedcalf) on 2021-02-18 12:50:58 in reply to 1 [link] [source]

select payload from dbstat where summary=1 and name='tbname';

(4) By ayagmur75 on 2021-02-18 13:02:11 in reply to 2 [link] [source]

What do you mean?

(5) By ayagmur75 on 2021-02-18 13:03:05 in reply to 3 [source]

There is no column named as summary.

(6) By Richard Hipp (drh) on 2021-02-18 13:45:09 in reply to 4 [link] [source]

I mean that you listed all the ways that I know of to get the size of a table. I do not know of any other way to do it. You have already tried everything.

(7) By curmudgeon on 2021-02-18 14:07:01 in reply to 5 [link] [source]

I think Keith means

select payload from dbstat('main',1) where name='tblname';

If it's an attached db replace 'main' with the attached schema name.

(8) By Keith Medcalf (kmedcalf) on 2021-02-18 14:24:17 in reply to 5 [link] [source]

That probably means you need a more recent version of SQLite. I don't recall offhand when summarization was added to the dbstat virtual table, but it is there now.

(9.1) By curmudgeon on 2021-02-18 14:42:49 edited from 9.0 in reply to 8 [link] [source]

sqlite> select payload from dbstat where summary=1 and name='big'; Error: no such column: summary

I get the same error Keith using 3.35.

summary replaced with aggregate works.

(10) By Keith Medcalf (kmedcalf) on 2021-02-18 14:50:21 in reply to 9.1 [link] [source]

Yes, you are correct. My mistake.

sqlite> pragma table_xinfo(dbstat);
┌─────┬────────────┬─────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │    name    │  type   │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼────────────┼─────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │            │         │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ name       │ TEXT    │ TEXT    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ path       │ TEXT    │ TEXT    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ pageno     │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ pagetype   │ TEXT    │ TEXT    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ ncell      │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ payload    │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ unused     │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ mx_payload │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ pgoffset   │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ pgsize     │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ schema     │ TEXT    │ TEXT    │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
│ 11  │ aggregate  │ BOOLEAN │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
└─────┴────────────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘