查询MYSQL、Oracle和达梦数据库表数据量
MYSQL
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'your_database_name' ORDER BY table_rows DESC;
Oracle
SELECT table_name, to_number(extractvalue(xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name)) ,'/ROWSET/ROW/C')) AS row_count FROM user_tables ORDER BY row_count DESC ;
达梦
SELECT B.OWNER,B.TABLE_NAME,TABLE_ROWCOUNT(B.OWNER,B.TABLE_NAME) "TABLE_ROWS" FROM (SELECT A.OWNER,A.TABLE_NAME FROM ALL_TABLES A WHERE A.TABLE_NAME NOT LIKE 'CTI%' AND A.TABLE_NAME NOT LIKE 'SREF_CON_TAB%' AND A.TABLE_NAME NOT LIKE 'BM%' ) B where B.OWNER='schema_name' ORDER BY 3 DESC,1,2;
表中存了多少数据
select table_schema as 数据库, table_name AS 表名, truncate(data_length/1024/1024,2) as 数据容量(MB), truncate(index_length/1024/1024,2) as 索引容量(MB) from information_schema.TABLES where TABLE_SCHEMA='数据库名' and TABLE_NAME='数据库中的表名' order by data_length desc, index_length desc;