Stored procedure MySQL for finding columns containing at least one value from a specific column

Question

I have a MySQL database with table and column names unknown in advance.

There is a table table_column_info, which contains the names of the tables and columns that need to be worked with.

There is an email_mapping table with system user logins.

I need to go through each table from table_column_info and check if at least one value from the login column is contained in the corresponding table column.

If there is at least one match in the table, then write the name of the table and column to the logins_map table.

The procedure must support partial occurrence (that is, the required login can be located inside the text and such a column also needs to be found).

I tried the following procedure but it doesn't work for me

CREATE PROCEDURE CheckLoginsAndInsert()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName, columnName VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT table_name, column_name FROM table_column_info;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName, columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Checking that values from the logins column are included in the corresponding table column
        SET @query = CONCAT(
            'INSERT INTO logins_map (table_name, column_name) ',
            'SELECT DISTINCT "', tableName, '", "', columnName, '" ',
            'FROM ', tableName, ' ',
            'WHERE EXISTS (SELECT 1 FROM email_mapping WHERE login LIKE CONCAT("%", ', columnName, ', "%"))'
        );

  
        SELECT @query;

  
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;
END //

Answer 1

The issue seems to be in your LIKE clause in your dynamic query,hope the updated SP helps

DELIMITER //

CREATE PROCEDURE CheckLoginsAndInsert()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName, columnName VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT table_name, column_name FROM table_column_info;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName, columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @query = CONCAT(
            'INSERT INTO logins_map (table_name, column_name) ',
            'SELECT DISTINCT "', tableName, '", "', columnName, '" ',
            'FROM `', tableName, '` ',
            'WHERE EXISTS (',
                'SELECT 1 FROM email_mapping ',
                'WHERE `', tableName, '`.`', columnName, '` LIKE CONCAT("%", email_mapping.login, "%")',
            ')'
        );


        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;


END //

DELIMITER ;