Excerpts from Richard Hipp's message of 2017-03-13 14:47:49 -0400:
> 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;

Worked pretty well, with the extension you mentioned. Thanks, Dr. Hipp

Marco Arthur @ (M)arco Creatives
sqlite-users mailing list

Reply via email to