A SQL procedure to query a column in all the tables

The procedure to query

DELIMITER $$
CREATE PROCEDURE FindCSourceRecords()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'c_source' 
    AND TABLE_SCHEMA = 'cbdb_data'; -- Replace with your database name
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

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

    -- Corrected dynamic SQL to properly execute and check for existence
    SET @s = CONCAT('SELECT EXISTS (SELECT 1 FROM ', tableName, ' WHERE c_source = 38299) INTO @exists;');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Check if the query found a row and then do something with the result
    IF @exists THEN
      -- This is a placeholder action; you might want to SELECT the table name or insert it into a log
      SELECT CONCAT('Found in table: ', tableName) AS Result;
    END IF;

  END LOOP;

  CLOSE cur;
END$$

DELIMITER ;

Call query procedure

CALL FindCSourceRecords();

Remove query procedure

DROP PROCEDURE IF EXISTS FindCSourceRecords;

The procedure to update

DELIMITER $$

CREATE PROCEDURE UpdateCSourceRecords()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'c_source' 
    AND TABLE_SCHEMA = 'cbdb_data'; -- Replace with your actual database name
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

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

    -- Dynamic SQL for updating c_source values
    SET @s = CONCAT('UPDATE ', tableName, ' SET c_source = 9334 WHERE c_source = 38299;');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE cur;
END$$

DELIMITER ;

Call update procedure

CALL UpdateCSourceRecords();

Remove update procedure

DROP PROCEDURE IF EXISTS UpdateCSourceRecords;