DROP PROCEDURE IF EXISTS `chanageCharSet`;

CREATE PROCEDURE `chanageCharSet`()

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE scheamName VARCHAR(100);

    DECLARE tableName VARCHAR(100);

    DECLARE columnName VARCHAR(100);

    DECLARE columnType VARCHAR(100);

    DECLARE alertSql VARCHAR(200);

    DECLARE _Cur CURSOR FOR (

            SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE

            FROM `information_schema`.`COLUMNS`

            WHERE DATA_TYPE='varchar' AND COLLATION_NAME <> 'utf8_general_ci' AND TABLE_SCHEMA like 'icop%'

    );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN _Cur;

    REPEAT

        FETCH _Cur INTO scheamName, tableName, columnName, columnType;

            IF NOT done THEN

                SET alertSql = CONCAT(

                    'ALTER TABLE `',

                    scheamName,

                    '`.`',

                    tableName,

                    '` MODIFY COLUMN `',

                    columnName,

                    '` ',

                    columnType,

                    ' CHARACTER SET utf8 COLLATE utf8_general_ci;'

                );

                SET @ESQL = alertSql;

                PREPARE stmt1 FROM @ESQL;

                EXECUTE stmt1;

                DEALLOCATE PREPARE stmt1;

            END IF;

    UNTIL done END REPEAT;

    CLOSE _Cur;

END;

CALL chanageCharSet();

DROP PROCEDURE IF EXISTS `chanageCharSet`;