I do not think there is a native command that does everything, you can create a query that does the job instead.
Tables
Generating the required queries for each table:
SELECT CONCAT(
"ALTER TABLE ",
TABLE_NAME,
" CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;")
FROM INFORMATION_SCHEMA.TABLES T
WHERE TABLE_TYPE="BASE TABLE"
AND TABLE_SCHEMA like '<schema>'
AND DATA_TYPE in ('varchar', 'text')
Now just copy and paste. Note the filter by DATA_TYPE to select only fields that have a collaction.
Databases
In the case of databases, I came up with the following command:
SELECT CONCAT(
"ALTER DATABASE ",
schema_name,
" CHARACTER SET utf8 COLLATE utf8_general_ci;")
FROM information_schema.schemata
WHERE schema_name LIKE '%'
To automate the process you will need to create a procedure or script and execute each command with EXECUTE .
A procedure template to be used as a base can be found here .
** Update **
With the information already provided above and with dynamic queries , I set up the following procedure to do the automatic conversion of the databases and their respective tables:
CREATE PROCEDURE change_collation()
BEGIN
DECLARE db VARCHAR(255);
DECLARE tab VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE done2 INT DEFAULT 0;
DECLARE databasesCursor CURSOR FOR
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN databasesCursor;
REPEAT
FETCH databasesCursor INTO db;
IF NOT done THEN
SET @q = CONCAT(
"ALTER DATABASE ",
db,
" CHARACTER SET utf8 COLLATE utf8_general_ci");
PREPARE stmt1 FROM @q;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
innerblock: BEGIN
DECLARE tablesCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = "BASE TABLE"
AND TABLE_SCHEMA = db
AND DATA_TYPE in ('varchar', 'text');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
OPEN tablesCursor;
REPEAT
FETCH tablesCursor INTO tab;
IF NOT done2 THEN
SET @q = CONCAT(
"ALTER TABLE ",
db, ".", tab,
" CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
PREPARE stmt1 FROM @q;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
UNTIL done2 END REPEAT;
CLOSE tablesCursor;
END;
END IF;
UNTIL tablesCursor END REPEAT;
CLOSE databasesCursor;
END//
To execute the procedure , just call it:
call change_collation();
Note: I could not properly test the above procedure because in SQLFiddle because an error occurs. Obviously they would not allow such commands on the server.
Final considerations
The procedure in this solution will list the databases and their tables, applying the change command to each one. Note, however, that there is no exceptional treatment, so if there is any problem, for example related to lack of permission, execution will be interrupted.
It is important to always consider the permissions used to execute both the procedure and the executed commands.
