mysql数据

USE pobo_mhall2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rows; CREATE TEMPORARY TABLE tmp_table_rows ( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS proc_get_all_rows; DELIMITER // CREATE PROCEDURE proc_get_all_rows() BEGIN DECLARE v_tbl VARCHAR(128); DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'pobo_mhall2' AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN cur; loop_fetch: LOOP FETCH cur INTO v_tbl; IF v_done = 1 THEN LEAVE loop_fetch; END IF; SET @sql = CONCAT('INSERT INTO tmp_table_rows(table_name,row_count) SELECT "',v_tbl,'",COUNT(*) FROM `',v_tbl,'`'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop_fetch; CLOSE cur; END // DELIMITER ; CALL proc_get_all_rows(); -- 查看全部结果，可复制导出 SELECT * FROM tmp_table_rows ORDER BY row_count DESC;

达梦数据库

USE POBO_MHALL2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rowcnt; CREATE TEMPORARY TABLE tmp_table_rowcnt( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS sp_fill_rowcnt; DELIMITER // CREATE PROCEDURE sp_fill_rowcnt() BEGIN DECLARE t_name VARCHAR(128); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='POBO_MHALL2' AND TABLE_TYPE='BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; READ_LOOP:LOOP FETCH cur INTO t_name; IF done = 1 THEN LEAVE READ_LOOP; END IF; SET @sql = CONCAT('INSERT INTO tmp_table_rowcnt(table_name,row_count) SELECT "',t_name,'",COUNT(*) FROM `',t_name,'`'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP READ_LOOP; CLOSE cur; END // DELIMITER ; CALL sp_fill_rowcnt(); -- 查询所有表，按行数倒序，可直接导出excel SELECT * FROM tmp_table_rowcnt ORDER BY row_count DESC;

人大金仓

DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pobo_mhall2' AND table_type = 'BASE TABLE' ORDER BY table_name LOOP EXECUTE format('SELECT COUNT(*) FROM %I.%I','pobo_mhall2',rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面代码同一个窗口，不要新开标签页！ SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;

海量数据

DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pobo_mhall2' AND table_type = 'BASE TABLE' ORDER BY table_name LOOP EXECUTE format('SELECT COUNT(*) FROM %I.%I','pobo_mhall2',rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面脚本一起执行，不要单独跑 SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;