可以查看所有数据库所有表的件数的SQL语句

这个SQL语句可以查看当前MySQL数据库Instance里所有数据库（Scheme）所有表的件数的SQL语句。

该语句会创建一个table_counts表来保存统计结果。创建一个存储过程CREATE PROCEDURE getRowCounts()来执行统计。

在dump之后用来对比数据迁移的结果非常有帮助！可以很容易地对比各个表里的数据量是否一致。

DROP TABLE IF EXISTS table_counts;
CREATE TABLE table_counts (database_name CHAR(64), table_name CHAR(64), row_count INT);

DROP PROCEDURE IF EXISTS getRowCounts;
DELIMITER //
CREATE PROCEDURE getRowCounts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a CHAR(64);
    DECLARE b CHAR(64);
    DECLARE cur CURSOR FOR SELECT table_schema, table_name
                           FROM information_schema.tables
                           WHERE table_schema NOT IN ('sys','information_schema', 'mysql', 'performance_schema') and TABLE_TYPE = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO a, b;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @s = CONCAT('INSERT INTO table_counts SELECT "', a ,'", "', b ,'", COUNT(*) FROM `', a, '`.`', b, '`');
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END;
//
DELIMITER ;

CALL getRowCounts();

SELECT * FROM table_counts where 1=1 order by database_name, table_name;