CODEWITHSUNDEEP
mysqlsql
gilbertbw
gilbertbw

Reputation: 690

How do I programmatically rename all tables in a database and prepend/append some text to the names of all of them?

I have a mySQL database of tables similar to:

users
tests
billing

and i am looking for an SQL query that cycles through tables in the database and appends application_ to the name, resulting in:

application_users
application_tests
application_billing

Upvotes: 2

Views: 713

Answers (2)

amrith
amrith

Reputation: 973

Depending on the Operating System you are using, have you considered writing a shell script that pipes the output of "show tables" command through sed to generate a script file with the appropriate alter table commands?

amrith@amrith-vbox:~/so$ more showtables.sql
show tables
amrith@amrith-vbox:~/so$ mysql -uroot -ppassword -N test < showtables.sql | awk '{ print "alter table " $1 " rename to abc_"$1 }'
alter table conversations rename to abc_conversations
alter table foo rename to abc_foo
alter table messages rename to abc_messages
alter table t1 rename to abc_t1
amrith@amrith-vbox:~/so$

You can send that output to some .sql file and then execute it using mysql?

Upvotes: 1

Bhavin Rana
Bhavin Rana

Reputation: 1582

RENAME TABLE  
   `database`.`table_1234` TO `database`.`new_table_1234`,  
   `database`.`table_3456` TO `database`.`new_table_3456`;

Upvotes: 0

Related Questions