How To Change Engine, Chartset, Collation For All Tables In A Db ?

[blondie63]

I’ve a db with 248 tables and i need to change engine, chartset, collation for all tables in a db

I’ve done is with this php script:

http://blog.tersmitten.nl/how-to-convert-a-mysql-database-and-all-its-tables-to-utf-8innodb.html

 

BUT

 

maybe this a good feature to add to Sqlyog 🙂

 

Blondie

[martin.lester]

I posted a note to your page. You can do that script in 1 query.

SELECT

CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' ENGINE = InnoDB;') AS alter_engine,

CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS alter_char

FROM information_schema.TABLES

WHERE

information_schema.TABLES.TABLE_SCHEMA = ''

AND (

information_schema.TABLES.ENGINE != 'InnoDB'

OR information_schema.TABLES.TABLE_COLLATION != 'utf8_general_ci'

)

ORDER BY

information_schema.TABLES.TABLE_SCHEMA

;

[Author]

Posts