Convert a table with same name in multiple databases to UTF8

March 3, 2018

Posted in:

DROP PROCEDURE IF EXISTS `ConvertTableToUTF8`;
DELIMITER $$

CREATE PROCEDURE `ConvertTableToUTF8`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

	DECLARE database_name VARCHAR(250);
	DECLARE all_databases CURSOR FOR 
		SELECT DISTINCT `table_schema`
			FROM `information_schema`.`tables`
			WHERE `table_name` = param_table_name;

	OPEN all_databases; 

	get_database: LOOP
		FETCH all_databases INTO database_name;
		SET @prep_query = CONCAT('ALTER TABLE `', database_name, '`.`', param_table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci'); 
		PREPARE statement1 FROM @prep_query ; 
		EXECUTE statement1; 
		DEALLOCATE PREPARE statement1;
	END LOOP get_database; 
	CLOSE all_databases; 
END$$

DELIMITER ;

CALL ConvertTableToUTF8('your_table_name');

Tags

Return to blog