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)
Recently I made a query that probably solves this, but it has some limitations and there are still issues unsolved. So, let's TableToCheck is table we want to know about. --------------------- CREATE TEMP TABLE [tblsizetemptable1] AS SELECT * FROM TableToCheck; CREATE TEMP TABLE [tblsizetemptable2] ([Id] INTEGER PRIMARY KEY); SELECT (SELECT rootpage FROM sqlite_temp_master WHERE tbl_name='tblsizetemptable2')- (SELECT rootpage FROM sqlite_temp_master WHERE tbl_name='tblsizetemptable1'); The result can be used as a page count size or be multiplied with the result of PRAGMA page_size; After that we should drop the tables DROP TABLE tblsizetemptable2; DROP TABLE tblsizetemptable1; I hope these queries are self-explanatory --------------------- So the questions and problems: - Is it possible to create a similar query for indexes. I mean the one, in what I only have to change the name of the index. As long as I understand there's no syntax looking like CREATE INDEX ... AS - If there are temp tables exist, the temp database file might be fragmented, so this method can give bad results. Is there something like VACUUM for the temp database or a similar actions leading to the compacted temp database? - Are there cases when a wrong result possible with ideal conditions (no temp database before)? - Such method is not good for large databases since all the data should be re-saved just to get the value. If someone suggest an algorithm solving this, this will be great. Thanks, Max _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
