mysql 查看各表的行数

1. 查看各表的实际数据行数

drop procedure if exists count_table_rows;

create procedure count_table_rows()
begin
    declare s int default 0;
    declare my_table_name varchar(500);
    declare my_cur cursor for select TABLE_NAME
                              from information_schema.TABLES
                              where TABLE_SCHEMA = '数据库名'
    declare continue handler for not found set s = 1;

    drop table if exists table_counts;
    create table table_counts
    (
        table_name varchar(128) null comment '表名',
        count      varchar(128) null comment '表中记录数'
    ) comment '表记录统计存储表';

    open my_cur;
    while s <> 1
        do
            fetch my_cur into my_table_name;
            set @tmp_sql = concat('insert table_counts select \'',
                                  my_table_name,
                                  '\' as table_name, count(*) as count from ',
                                  my_table_name, ';');
            prepare stm from @tmp_sql;
            execute stm;
            deallocate prepare stm;
        end while;
    close my_cur;
end;

call count_table_rows;