sqlite查询以获取其中包含记录数的所有表名列表 [英] sqlite query to get all list of table names with number of records in it

问题描述

请帮助解决以下查询:sqlite 查询以获取其中包含记录数的所有表名列表:

Please help on below query : sqlite query to get all list of table names with number of records in it :

我想获取 Sqlite3 数据库中每个表中的行数.我想避免写出一个普通的查询.我可以得到这样的表列表:

I want to get the count of rows in every table in a Sqlite3 database. I want to avoid writing out a longhand query. I can get the list of tables like this:

SELECT name FROM sqlite_master WHERE type='table'我想在这样的子查询中使用它:

SELECT name FROM sqlite_master WHERE type='table' and I would like to use it in a subquery like this:

select count (*) from (SELECT name FROM sqlite_master WHERE type='table');但只会返回子查询中的总行数，这不是我想要的.

select count (*) from (SELECT name FROM sqlite_master WHERE type='table'); but would just return the total rows in the subquery, which isn't what I want.

推荐答案

也许你用了 ANALYZE 以创建解决方法.它创建内部架构对象 sqlite_stat1

2.6.3.sqlite_stat1 表

sqlite_stat1 是 ANALYZE 命令创建的内部表并用于保存有关表和索引的补充信息查询计划器可以用来帮助它找到更好的方法执行查询.应用程序可以更新、删除、插入或删除 sqlite_stat1 表，但不能创建或更改sqlite_stat1 表.sqlite_stat1 表的架构如下如下:

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);

通常每个索引有一行，索引由sqlite_stat1.idx 列中的名称.sqlite_stat1.tbl 列是索引所属的表的名称.在每一行中，sqlite_stat.stat 列将是一个字符串组成的列表整数后跟零个或多个参数.此中的第一个整数list 是索引中的大致行数.(数量索引中的行数与表中的行数相同，部分索引除外.) .....

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

If there are no partial indexes, the SELECT tbl,cast(stat as INT) will return the number of rows in each table, unless the table has 0 rows.

这个 sql 在一个小型(25MB，34 个表，26 个索引，33K+ 行)生产数据库上给出了预期的结果.您的里程可能(会?)有所不同.

This sql gives the expected results on a small (25MB, 34 tables, 26 indexes, 33K+ rows) production database. Your mileage may (will?) vary.

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;

这篇关于sqlite查询以获取其中包含记录数的所有表名列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！