MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version()

8.0.20

DROP PROCEDURE IF EXISTS GetAccounts; 
DELIMITER // 
CREATE PROCEDURE GetAccounts() 
BEGIN 
DECLARE rowcount int;
    SELECT * from requests where STATUS = "FAILURE" ;
END // 
DELIMITER ;
SELECT * FROM INFORMATION_SCHEMA;
Table 'db_1294574668.INFORMATION_SCHEMA' doesn't exist

/* Create INFORMATION_SCHEMA table(not all columns included,only for demo) as dbfiddle doesnt have access to meta data table*/
create table information_Schema (table_name char(64),column_name char(64));
/* Valid Tables are TESTA and TESTB */
/* COLUMNA will be searched for column values 'value0' */
insert into information_Schema values('TESTA','COLMNA');
insert into information_Schema values('TESTA','COLMNC');
insert into information_Schema values('TESTB','COLMNA');
insert into information_Schema values('TESTB','COLMNB');
insert into information_Schema values('TESTC','COLMNA');
insert into information_Schema values('TESTC','COLMNE');
create table TESTA(COLMNA char(255),COLMNC char(255));
create table TESTB(COLMNA char(255),COLMNB char(255));
create table TESTC(COLMND char(255),COLMNA char(255));
insert into TESTA values('value0','someothercolmn');
insert into TESTB values('value0','anothersomeothercolmn');
insert into TESTB values('value1','Yetanothercolumn');
SELECT table_name
     FROM information_Schema  WHERE column_name = 'COLMNA';

table_name

TESTA

TESTB

TESTC

select * from TESTA;

COLMNA
COLMNC

value0
someothercolmn

Select * from TESTB;

COLMNA
COLMNB

value0
anothersomeothercolmn

value1
Yetanothercolumn

Select * from TESTC;
CREATE PROCEDURE Findtables( colmn_name VARCHAR(50),colmn_value VARCHAR(50) )

BEGIN

   DECLARE tablename CHAR(64);

   DECLARE c1 CURSOR FOR
     SELECT table_name
     FROM information_Schema
     WHERE column_name = colmn_name;

   OPEN c1;
    lable_loop:LOOP
   FETCH c1 INTO tablename;
      
     select tablename;
     SET @sql = CONCAT('SELECT * FROM  ', tablename, ' WHERE ',colmn_name,' = "',colmn_value ,'" ;'); 
     
        PREPARE stmt FROM @sql;
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt;
      
     END LOOP lable_loop;  
   CLOSE c1;

END; 
CALL Findtables('COLMNA','value0');

tablename

TESTA

COLMNA
COLMNC

value0
someothercolmn

tablename

TESTB

COLMNA
COLMNB

value0
anothersomeothercolmn

tablename

TESTC

select * from TESTA;

COLMNA
COLMNC

value0
someothercolmn

select * from TESTB;

COLMNA
COLMNB

value0
anothersomeothercolmn

value1
Yetanothercolumn