Statistics, even if generated at run-time, might be useful. One option, at least per the CLI, might be to output a variant of DRH's last SQL to be read back in. I've no idea as to the portability of this onto embedded systems, but it works "okay" on the CLI and on desktop apps...my test file was fairly large and I'm including views, which add to the overhead.
SELECT CASE WHEN rowid == (select max(rowid) from sqlite_master where type in ('table','view') and name not like 'sqlite_%') THEN printf('SELECT "%w", count(*) FROM "%w" ',name,name) ELSE printf('SELECT "%w", count(*) FROM "%w" UNION',name,name) END counted FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ; Dynamic SQL would be very helpful here, but I haven't seen it on SQLite. Maybe if an attached db could be leveraged...? Regards. Brian P Curley On Mon, Mar 13, 2017 at 2:47 PM, Richard Hipp <d...@sqlite.org> wrote: > On 3/13/17, Marco Silva <marco.prado...@gmail.com> wrote: > > Hi, > > > > Does anyone knows a Common Table Expression (CTE) to be used with the > > sqlite_master table so we can count for each table how many rows it > > has. > > That is not possible. Each table (potentially) has a different > structure, and so table names cannot be variables in a query - they > must be specified when the SQL is parsed. > > But you could do this with an extension such as > https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements > an SQL function that submits new SQL text to the SQLite parser. For > example: > > SELECT name, eval(printf('SELECT count(*) FROM "%w"',name)) > FROM sqlite_master > WHERE type='table' AND coalesce(rootpage,0)>0; > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users