On Mon, Mar 13, 2017 at 7: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; > You can also use SQLite to generate SQL text which you feed back into SQLite :) Below's my little experimentation on that. Nothing new, but was fun nonetheless. --DD C:\Users\ddevienne>sqlite3.18.0rc test.db SQLite version 3.18.0 2017-03-06 20:44:13 Enter ".help" for usage hints. sqlite> create table t (id); sqlite> create table u (id); sqlite> create table v (id); sqlite> insert into t values (1), (2), (3); sqlite> insert into u values (4), (5); sqlite> .exit C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from t"; 3 C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from u"; 2 C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from v"; 0 C:\Users\ddevienne>sqlite3.18.0rc test.db "select 'select '''||name||''' as name, count(*) from '|| name from sqlite_master where type = 'table'" select 't' as name, count(*) from t select 'u' as name, count(*) from u select 'v' as name, count(*) from v C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select '''||name||''' as name, count(*) from '|| name, ' union all ') from sqlite_master where type = 'table' group by type" select 't' as name, count(*) from t union all select 'u' as name, count(*) from u union all select 'v' as name, count(*) from v C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select '''||name||''' as name, count(*) from '|| name, char(10)||'union all'||char(10)) from sqlite_master where type = 'table' group by type" select 't' as name, count(*) from t union all select 'u' as name, count(*) from u union all select 'v' as name, count(*) from v C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select '''||name||''' as name, count(*) from '|| name, ' union all ') from sqlite_master where type = 'table' group by type" | sqlite3.18.0rc test.db t|3 u|2 v|0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
