I took me a while to find out that it’s not very easy to find out how big a sqlite table is on disk. But luckily the sqlite creators have a simple tool for that. It’s called sqlite3_analyzer and is available in precompiled format for Linux, Windows and OSX on SQLite’s Download section
Confusing: There also seems to be a commercial graphical sqlite tool in a windows only variant that has the same name, but it doesn’t have functionality to see how big tables are. http://www.kraslabs.com/sqlite_analyzer.php
When you run sqlite3_analyzer it will create a .sql file with info about the database. You can insert the .sql file in a new database to get more detailed info about the tables sizes and some other metrics.
It will produce a sql file with a big sql comment on top. After the comment some SQL statements are printed that will create a table “space_used” with more detailed info.
Example: the example DB is 381Mb on disk. The Binary table is used to store some binary data.
I used it to clean up some DB’s with a lot of tables. After deleting a lot of records I noticed that the file size didn’t shrink, so as a reminder for myself; you have to execute one SQLite SQL statement to free up disk space after a lot of delete statements: VACUUM;
Quick Links
Legal Stuff