Check NOW field content counts

[karilint]

Activity Show details Write a comment… Kari Lintulaakso Dec 7, 2023 11:39 AM There are several table columns which have no value. even some tables are empty. They are possibly not used by NOW.

Edit • Delete Kari Lintulaakso Dec 7, 2023 11:38 AM -- Drop the existing procedures DROP PROCEDURE IF EXISTS GetAllTableAndColumnCounts; DROP PROCEDURE IF EXISTS ProcessTableColumns;

DELIMITER //

CREATE PROCEDURE GetAllTableAndColumnCounts() BEGIN DECLARE doneTables INT DEFAULT FALSE; DECLARE tableName VARCHAR(255); DECLARE curTable CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'now' AND table_type = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneTables = TRUE;

-- Create a temporary table to store the results DROP TEMPORARY TABLE IF EXISTS temp_counts; CREATE TEMPORARY TABLE temp_counts ( table_name VARCHAR(255), column_name VARCHAR(255), count_table_rows INT, count_column_values INT );

OPEN curTable;

table_loop: LOOP FETCH curTable INTO tableName; IF doneTables THEN LEAVE table_loop; END IF;

-- Process each column for the current table
CALL ProcessTableColumns(tableName);

END LOOP;

CLOSE curTable;

-- Select the results from the temporary table SELECT * FROM temp_counts;

-- Drop the temporary table DROP TEMPORARY TABLE IF EXISTS temp_counts; END //

-- Procedure to process columns of a given table CREATE PROCEDURE ProcessTableColumns(IN currentTable VARCHAR(255)) BEGIN DECLARE doneColumns INT DEFAULT FALSE; DECLARE columnName VARCHAR(255); DECLARE curColumn CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_schema = 'now' AND table_name = currentTable; DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneColumns = TRUE;

OPEN curColumn;

column_loop: LOOP FETCH curColumn INTO columnName; IF doneColumns THEN LEAVE column_loop; END IF;

-- Insert the count data into the temporary table
SET @s = CONCAT('INSERT INTO temp_counts (table_name, column_name, count_table_rows, count_column_values) SELECT "', currentTable, '", "', columnName, '", (SELECT COUNT(*) FROM ', currentTable, '), COUNT(', columnName, ') FROM ', currentTable);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END LOOP;

CLOSE curColumn; END //

DELIMITER ;

CALL GetAllTableAndColumnCounts();

[karilint]

20231207_GetAllTableAndColumnCounts.xlsx