How to find the count of nulls value of all the columns in a SQL table using procedures/UDF

Question

I want to find null values of columns of SQL table using procedures/UDF. We tried to find the null columns using case expression.

select sum(case when a is null then 1 else 0 end) count_a_nulls, 
sum(case when b is null then 1 else 0 end) count_b_nulls,
sum(case when c is null then 1 else 0 end) count_c_nulls
from dummy;

Here the problem is that we don't want to put columns manually. If there are 50+ columns, we will have to add too many case statements.

Here is a dummy table.

Output

Answer 1

Somehow i am able to write stored procedure to find the null count of columns of a table.

DELIMITER //
CREATE PROCEDURE `passTableDynamic5`(tables_name TEXT)
BEGIN
    DECLARE done INT DEFAULT 0;
   DECLARE column_name_value VARCHAR(100) DEFAULT NULL; 
    DECLARE case_expression VARCHAR(500) DEFAULT NULL;
    DECLARE SQL_QUERY  VARCHAR(500) DEFAULT NULL; 
   DECLARE csr_var_list CURSOR FOR SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_NAME=tables_name;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   SET case_expression ='';
   OPEN csr_var_list;
   get_id: LOOP
      FETCH csr_var_list INTO column_name_value; 
      SELECT CONCAT("coulm_name", done);
      
      IF done = 1 THEN
        SELECT CONCAT("coulm_name", done);
        SET case_expression =CONCAT(case_expression, ' sum(', column_name_value,' is NULL) count_',column_name_value);
         LEAVE get_id;
      END IF;
      
      IF done = 0 THEN
        SET case_expression = CONCAT(case_expression,' sum(', column_name_value,' is NULL) count_',column_name_value, ',');
      END IF;
   END LOOP get_id;
   
   CLOSE csr_var_list;
   
   SET SQL_QUERY = CONCAT('SELECT ', case_expression, ' From ', tables_name, ';');
   
   PREPARE stmt FROM SQL_QUERY;
   EXECUTE stmt;
   
END//
DELIMITER ;

Use this stored procedure like this passTableDynamic5('table_name')