CODEWITHSUNDEEP
mysqlsql
TotoNaBendo
TotoNaBendo

Reputation: 33

Alter all table with prefixe

I'm looking for a SQL code allowing to alter all table with a prefixe word :

I got many table names XXXX_users and i would like to add a column to each. Is it possible to do it with just one row with something like :

ALTER TABLE %_users

I use PHPMyAdmin for the database management.

Thanks in advance

Upvotes: 0

Views: 76

Answers (1)

Olá Òké - Ola Oke
Olá Òké - Ola Oke

Reputation: 74

For 'dynamic MySQL' ('dynamic SQL'), auto generate the ALTER statements with this:

   SELECT CONCAT('RENAME TABLE ', table_name, ' TO XXXX_', table_name, ';' ) alter_table_stmt
     FROM INFORMATION_SCHEMA.tables t
    WHERE TABLE_SCHEMA IN ('<DB SCHEMA NAME>');

then execute the generated statements as a batch.

Upvotes: 2

Related Questions