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 <[email protected]> wrote:
> On 3/13/17, Marco Silva <[email protected]> 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
> [email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
