MariaDB set row_format to dynamic to all tables in one command

Question

i want to change the row_format to dynamic on all tables in my database. When the datebase is selected i could do "ALTER TABLE tablename ROW_FORMAT=DYNAMIC;" to do it manually. Unfortunately there are around 100 tables to be changed.

How can i change the row format to dynamic on every tables in a specific DB that has something different to DYNAMIC?

I've been trying it but i cant find a working solution.

Answer 1

You can't ALTER TABLE more than one table at a time, but you can generate all the necessary ALTER TABLE statements this way:

SELECT CONCAT(
  'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
  'ROW_FORMAT=DYNAMIC;'
) AS _alter
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='InnoDB' AND ROW_FORMAT <> 'DYNAMIC';

Capture the output of that and run it as an SQL script.