mysql: stored procedure block to get table row counts

 In Mysql we cannot use PLSQL. we need to make use of procedures to get the desired results that we need.

To get the table row counts of all the tables in mysql. Use below code.  Put below code in *.sql file and run as "source *.sql" after logging into mysql.

added a few check points to see the proc simulation.

------ code  

use mysql

drop procedure TestExampleProc2;

DELIMITER //
CREATE PROCEDURE TestExampleProc2()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE tabname,table_n,tabsch,tabcount,tabsch_n VARCHAR(70);
      DECLARE cur CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables where table_schema not in ('mysql','Performance_schema','sys','information_schema') and table_type = 'BASE TABLE'  order by table_schema asc;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN cur;
      label: LOOP
      FETCH cur INTO tabsch,tabname;
      set @table_n = tabname;
      set @tabsch_n = tabsch;
      set @query = CONCAT('select count(*) into @tabcount from ', @tabsch_n,".",@table_n);
     -- select @tabsch_n,@table_n;
     -- select @query;
      PREPARE stmt1 FROM @query;
      execute stmt1;
     -- select @query;
      select tabsch,tabname,@tabcount;
      DEALLOCATE PREPARE stmt1;
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;
      CLOSE cur;
   END//
DELIMITER ;

tee  mysql_table_count.log

call TestExampleProc2();

notee;

----------------------------- code ends

this will create a logfile with all the table counts.