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 test file was fairly large and I'm including views, which add to
the overhead.


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





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...?


Brian P Curley

On Mon, Mar 13, 2017 at 2:47 PM, Richard Hipp <> wrote:

> On 3/13/17, Marco Silva <> 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
> 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
> _______________________________________________
> sqlite-users mailing list
sqlite-users mailing list

Reply via email to