postgresql中:
select relname as TABLE_NAME, reltuples as rowCounts from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;
但是在使用某为的并没有结果,因为只是单次使用就没深入研究了,使用拼接来解决:
1. 获取sql:
注意这里得到的sql语句如:select 'bg_ssjlbg', count(*) from bg_ssjlbg union all 表必须使用单引号来包围,在如下sql中使用2个单引号代表一个,myslq可以使用双引号,如:postgresql处理:'''111''' -> '111'
select concat(
'select ''',
relname,
''', count(*) from ',
relname,
' union all '
)
from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public');
2. 复制结果为sql执行,再执行。
mysql:
在mysql中使用:
SELECT TABLE_NAME AS `表名`,TABLE_ROWS AS `行数`,date_format(CREATE_TIME,'%Y-%m-%d') AS `上传时间` FROM information_schema.TABLES WHERE TABLE_SCHEMA='查询的数据库' AND TABLE_rows>0 ORDER BY TABLE_ROWS DESC;
但是这只是一个粗略的统计,实际在使用中,一个表实际有3575行,但是information_schema.TABLES统计出来只有2606。
所以这里使用sql拼接比较方便。
一、执行下面sql
select concat(
'select "',
TABLE_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
TABLE_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA='test';
二、执行上面sql后会批量生成sql导出结果,复制到一个文件中,然后单个表行数:
select "MJZBL_JZLGBL", count(*) from emr.MJZBL_JZLGBL union all
select "MJZBL_MJZBL", count(*) from emr.MJZBL_MJZBL union all
select "MZZY_FYJL", count(*) from emr.MZZY_FYJL union all
select "MZ_CFMXB", count(*) from emr.MZ_CFMXB
三、统计所有行数:
SELECT SUM(`COUNT(*)`) FROM (
select "MJZBL_JZLGBL", count(*) from emr.MJZBL_JZLGBL union all
select "MJZBL_MJZBL", count(*) from emr.MJZBL_MJZBL union all
select "MZZY_FYJL", count(*) from emr.MZZY_FYJL union all
select "MZ_CFMXB", count(*) from emr.MZ_CFMXB ) a;
总结:
SELECT sum(TABLE_ROWS)
FROM information_schema.TABLES WHERE TABLE_SCHEMA='emr' ;
结果为:1004801
使用上面方法统计库总行数:1000311