On Fri, Jul 16, 2010 at 02:01:27PM +0400, Max Vlasov scratched on the wall: > Hi, > > always wanted to have a possibility to calculate how much a table occupies. > As long as I see from the archive, there's no out-of-the-box solution > (CMIIW)
Check out sqlite3_analyzer. This is available in binary format only on the SQLite website. http://sqlite.org/download.html In addition to a large number of database file stats, it provides the following information for every table and index: name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages gap_cnt int -- Number of gaps in the page layout > Recently I made a query that probably solves this, but it has some > limitations and there are still issues unsolved. > - Are there cases when a wrong result possible with ideal conditions (no > temp database before)? The reported size is likely smaller than the table, as it sits in the original database. This technique defragements and re-packs the pages, not unlike the VACUUM command. It should give you pretty good numbers for the VACUUMed size, but not for the current size. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
