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 [link] [source]
There is no column named as summary.
(6) By Richard Hipp (drh) on 2021-02-18 13:45:09 in reply to 4 [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 │
└─────┴────────────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘