I need to run a procedure that goes through all schemas tables in MySQL server I am new in creating procedures ,Actually I need to run a procedure that will run on all schemas tables in my server when am calling the procedure it gives me this error


  ERROR 1046 (3D000): No database selected


DELIMITER $$
CREATE PROCEDURE SET_UTF_ALL_TABLES()
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(150);
DECLARE _counter INT DEFAULT 0;
DECLARE row_number INT;

DECLARE cur1 CURSOR FOR SELECT TABLE_NAME from information_schema.tables             
where TABLE_SCHEMA IN ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') ;

SELECT COUNT(*) from information_schema.tables where TABLE_SCHEMA IN 
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') INTO row_number;

OPEN cur1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

read_loop: LOOP
   FETCH cur1 INTO a;
IF done THEN
    LEAVE read_loop;
END IF;
IF _counter &lt; row_number THEN
    ALTER TABLE a CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    SET _counter = _counter + 1;
END IF;
END LOOP;

CLOSE cur1;
END;
DELIMITER;
Selecting TABLE_NAME is not enough. You need CONCAT(TABLE_NAME, '.', TABLE_SCHEMA).
You cannot use a variable in a query as a place where tablename is stored. You must use dynamic SQL (prepared statement).


Prepared SQL Statement Syntax


  I need now to loop on each table how could I do that in prepared statement


Get each database.table which must be converted. Build proper altering query text in a variable. Prepare. Execute. Iterate next. Repeat until all tables processed. - akina