Search code examples
mysqlsqlalter

ALTER TABLE ALL TABLES ROW_FORMAT=Fixed;

How can I run a command for all tables in a database?

ALTER TABLE table_name ROW_FORMAT=Fixed;

I have more than 40000 tables, I want to change them all in one go.

Solution

  • You cannot alter table more than one table per ALTER statement; but you can use a query on information_schema.tables to generate the alter statements.

    Something like

    SELECT CONCAT('ALTER TABLE `', table_name, '` ROW_FORMAT=fixed;') AS aQuery
FROM information_schema.tables
WHERE table_schema = 'myschema'

    Keep in mind FIXED is not supported for InnoDB tables, and I am not 100% sure if it can even be explicitly set for MyISAM ones (or is entirely dependent on the table's columns' data types.)