eawgXxUf

1DELIMITER $$
2DROP PROCEDURE IF EXISTS `displayNullColumns`$$
3CREATE PROCEDURE `displayNullColumns`()
4  BEGIN
5    DECLARE rowCount INT;
6    DECLARE currentRow INT;
7
8    SET @expression = '';
9    SET currentRow = 0;
10    SET rowCount = (
11      SELECT COUNT(DISTINCT CONCAT(TABLE_NAME, COLUMN_NAME))
12      FROM INFORMATION_SCHEMA.COLUMNS
13    );
14
15    myLoop: WHILE (currentRow < rowCount) DO
16      IF currentRow = 0
17      THEN
18        SET @expression = (
19          SELECT CONCAT('SELECT ', s._column, ' AS _value, "', s._column, '" AS _column, "', s._table, '" AS _table FROM ', s._table, ' WHERE ', s._column, ' = "null"')
20          FROM (
21                 SELECT DISTINCT
22                   COLUMN_NAME AS `_column`,
23                   TABLE_NAME  AS `_table`
24                 FROM INFORMATION_SCHEMA.COLUMNS
25                 WHERE TABLE_SCHEMA = 'dev_productreview'
26                       AND COLUMN_NAME IS NOT NULL
27                 LIMIT 1
28                 OFFSET currentRow
29               ) AS s
30        );
31      ELSE
32        SET @expression = (
33          SELECT CONCAT(@expression, ' UNION SELECT ', s._column, ' AS _value, "', s._column, '" AS _column, "', s._table, '" AS _table FROM ', s._table, ' WHERE ', s._column, ' = "null"')
34          FROM (
35                 SELECT DISTINCT
36                   COLUMN_NAME AS `_column`,
37                   TABLE_NAME  AS `_table`
38                 FROM INFORMATION_SCHEMA.COLUMNS
39                 WHERE TABLE_SCHEMA = 'dev_table'
40                       AND COLUMN_NAME IS NOT NULL
41                       AND TABLE_NAME IS NOT NULL
42                 LIMIT 1
43                 OFFSET currentRow
44               ) AS s
45        );
46      END IF;
47
48      SET currentRow = currentRow + 1;
49
50    END WHILE myLoop;
51
52    IF @expression IS NOT NULL AND @expression != ''
53    THEN
54      PREPARE stmt FROM @expression;
55      EXECUTE stmt;
56    END IF;
57  END$$
58DELIMITER ;