SQL RENAME TABLE command

Question

Hello. I can run RENAME TABLE student TO student_new ;
The command is same and easy to follow.

Is there a methods to rename a lot of tables in simple command. Assume all the tables belog to the same DB name.

I don't need write a lot of code as below?

RENAME TABLE pre_access TO pre_new_access; 
RENAME TABLE pre_activities TO pre_new_activities;
RENAME TABLE pre_activityapplies TO pre_new_activityapplies;
RENAME TABLE pre_adminactions TO pre_new_adminactions;
RENAME TABLE pre_admincustom TO pre_new_admincustom;
RENAME TABLE pre_admingroups TO pre_new_admingroups;
RENAME TABLE pre_adminnotes TO pre_new_adminnotes;
...

(there are still so many tables need to be renamed)

Update: MySQL Used.

Answer 1

You could always generate a simple PHP script that loops through the databases and renames them based on a REGEX expression.

Answer 2

Assuming from your comment that you use MySQL, you should be able to "select" the necessary statements from information_schema.tables which contains a list of your tables.

Replace your_schema by your schema-name and check the selected rows before executing them.

Select Concat( 'RENAME TABLE ', table_name, ' TO ', 'new_', table_name, ';' )
From information_schema.tables
Where table_schema = 'your_schema';

returns

RENAME TABLE c_data TO new_c_data;
RENAME TABLE c_main TO new_c_main;
...

Answer 3

You don't mention what database you're using, and this is completely database dependent (some DBMSes don't even allow you to rename tables). However, I don't know of any DBMS system off the top of my head that allows wildcards in a RENAME command so yes, you will probably have to send each command separately.

Answer 4

for mysql database "test" should work:

echo "show tables"|mysql test|perl -ne 'if(/pre_(\w+)/){print "rename table pre_$1 to pre_new_$1;\n"}'|mysql test