SQLite查询以获取所有表名及其记录数

Question

请帮忙查询以下内容： 获取SQLite查询以获取所有表名称及其中记录数量：

我想在Sqlite3数据库中获取每个表中行数的计数。我想避免编写冗长的查询语句。我可以像这样获取表格列表: SELECT name FROM sqlite_master WHERE type='table'， 并希望将其用于子查询中，如下所示: select count (*) from (SELECT name FROM sqlite_master WHERE type='table'); 但这将只返回子查询中的总行数，这不是我想要的结果。

- Bhushan Muttha

---

由于在查询准备阶段必须知道表名，因此您必须分步骤进行。获取表名列表并循环遍历，每个表运行一个查询，每次将表名插入查询字符串中。 - Shawn

是的，在循环中获取数据是一种方法。我正在寻找一个单一的查询，可以返回结果数据。如果可以对“sqlite_master”执行一些内部连接操作。 - Bhushan Muttha

你不能在单个查询中完成它。 - Shawn

谢谢，如果我找到解决方法，我会更新的。 - Bhushan Muttha

Answer 1

也许您会使用 ANALYZE 的结果来创建解决措施。它会创建内部模式对象sqlite_stat1。

2.6.3. The sqlite_stat1 table

The sqlite_stat1 is an internal table created by the ANALYZE command and used to hold supplemental information about tables and indexes that the query planner can use to help it find better ways of performing queries. Applications can update, delete from, insert into or drop the sqlite_stat1 table, but may not create or alter the sqlite_stat1 table. The schema of the sqlite_stat1 table is as follows:

CREATE TABLE sqlite_stat1(tbl,idx,stat);

There is normally one row per index, with the index identified by the name in the sqlite_stat1.idx column. The sqlite_stat1.tbl column is the name of the table to which the index belongs. In each such row, the sqlite_stat.stat column will be a string consisting of a list of integers followed by zero or more arguments. The first integer in this list is the approximate number of rows in the index. (The number of rows in the index is the same as the number of rows in the table, except for partial indexes.) .....

如果没有部分索引，SELECT tbl,cast(stat as INT)会返回每个表中的行数，除非该表为0行。

在一个小型（25MB，34个表，26个索引，33K+行）生产数据库上，此SQL可以得到预期的结果。但是可能因情况而异。

ANALYZE;
select  DISTINCT tbl_name, CASE WHEN stat is null then 0 else cast(stat as INT) END numrows 
from sqlite_master m 
LEFT JOIN sqlite_stat1 stat on   m.tbl_name = stat.tbl 
where m.type='table'
and m.tbl_name not like 'sqlite_%'
order by 1;
--drop table sqlite_stat1;

- DinoCoderSaurus