drop table if exists t1;
create  table t1
(tbl_name VARCHAR(200),
 col_name VARCHAR(200),
 col_type VARCHAR(200),
 c_all INT,
 c_null INT
);

DROP PROCEDURE IF EXISTS sp1;

DELIMITER  //

create PROCEDURE `sp1`()
begin
  declare c_all, c_null INT;
  declare sql_str VARCHAR(2000);
  DECLARE done INT DEFAULT 0;
 
  declare CUR cursor for
  select 
   concat(
     'insert into t1 select ''',
       C.TABLE_NAME,''' tbl_name, ''',
       C.COLUMN_NAME,''' col_name, ''',
       REPLACE(C.COLUMN_TYPE,'''','"'),''' col_type, ',
     ' count(1) c_all,',
     ' sum(',COLUMN_NAME,' is NULL) c_null',
     ' from ', C.TABLE_NAME
   ) sql_str
from information_schema.COLUMNS C
JOIN information_schema.TABLES T 
ON C.TABLE_NAME = T.TABLE_NAME 
where C.TABLE_SCHEMA = 'sakila'   -- use your schema
AND TABLE_TYPE = 'BASE TABLE';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open CUR;
cursor_label:
LOOP FETCH CUR into sql_str;
        IF (1 = done)  THEN
            LEAVE cursor_label;
        END IF;

    -- select sql_str; -- debug
    set @s = sql_str;
    IF LENGTH(@s) < 10   THEN
        LEAVE cursor_label;
    END IF;
    PREPARE stmt FROM @s;
    EXECUTE stmt;

END LOOP cursor_label;
CLOSE CUR;

select * from t1 ;
end //
DELIMITER ;

call sp1;