Quantcast

A CTE to count the records (rows) for each table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

A CTE to count the records (rows) for each table

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

--
Marco Arthur @ (M)arco Creatives
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Simon Slavin-3

On 13 Mar 2017, at 6:40pm, Marco Silva <[hidden email]> wrote:

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

The way SQLite stores its data is not helpful to counting the rows.  The number of rows isn’t stored anywhere and to find out the number of rows in a table SQLite has to iterate through them all.

The fastest way to find the number of rows in a table is

SELECT COUNT(*) FROM MyTable

and I’m afraid there is no simple command which does this for more than one table.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Richard Hipp-3
In reply to this post by Marco
On 3/13/17, Marco Silva <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Brian Curley
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 <[hidden email]> wrote:

> On 3/13/17, Marco Silva <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Dominique Devienne
In reply to this post by Richard Hipp-3
On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp <[hidden email]> wrote:

> On 3/13/17, Marco Silva <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Dominique Devienne
On Tue, Mar 14, 2017 at 10:14 AM, Dominique Devienne <[hidden email]>
wrote:

>
> On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp <[hidden email]> wrote:
>
>> On 3/13/17, Marco Silva <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Marco
In reply to this post by Richard Hipp-3
Excerpts from Richard Hipp's message of 2017-03-13 14:47:49 -0400:

> On 3/13/17, Marco Silva <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Donald Griggs
> >  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.

I wonder if it's always accurate to piggyback on the work of ANALYZE and
obtain row counts as of the last ANALYZE via:

       select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
sqlite_stat1 group by tbl order by tbl;

Equivalently, if one relies on CAST to obtain the first integer:

        select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
tbl order by tbl;


>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A CTE to count the records (rows) for each table

Richard Hipp-3
On 3/15/17, Donald Griggs <[hidden email]> wrote:

>> >  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.
>
> I wonder if it's always accurate to piggyback on the work of ANALYZE and
> obtain row counts as of the last ANALYZE via:
>
>        select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
> sqlite_stat1 group by tbl order by tbl;
>
> Equivalently, if one relies on CAST to obtain the first integer:
>
>         select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
> tbl order by tbl;

The current ANALYZE always makes an exact row-count.  But there is
code on a branch
(https://www.sqlite.org/src/timeline?r=est_count_pragma) that only
does an approximation.  And that "approximate" ANALYZE may land on
trunk within the next release or two.

So, no, I would not trust the sqlite_stat1 data if you need an accurate count.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...