I need to run a procedure that goes through all schemas tables in MySQL server

by Eng.Gabr   Last Updated June 13, 2019 09:06 AM

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 < 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;
Tags : mysql plsql


Related Questions


Updated September 07, 2017 13:06 PM

Updated September 07, 2017 15:06 PM

Updated February 01, 2017 14:02 PM

Updated May 14, 2015 21:02 PM

Updated March 06, 2016 02:02 AM