On Tue, Mar 14, 2017 at 10:14 AM, Dominique Devienne <ddevie...@gmail.com> wrote:
> > 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 "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 > > And thinking more about this, what we'd need is a new .eval command to the official shell, so we don't have to use two instances of SQLite on the same DB connected via a pipe. .eval would run the SQL that follows as usual, but each output row of that SQL should start with a text value being to SQL text to evaluate, and we could even imagine optional extra columns being the bind parameters of that SQL text, if bind placeholders are used. Now that would be fun and powerful. That's not dynamic SQL in SQLite itself, only the shell, but that would already be very powerful IMHO. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
