In MySQL, how do I batch rename tables within a database?

Question

So basically I have a joomla database in MySQL which has a bunch of tables that have the prefix 'jmla_'. I would like to rename all of these tables by replacing the 'jmla_' prefix with a 'jos_' prefix. Any ideas about how to do this with a simple SQL script or SQL query?

Answer 1

SELECT  concat ('rename table ',table_name,' to ',table_name,'_old;')
FROM information_schema.tables
WHERE table_name like 'webform%'
  and table_schema='weiss_db_new'

will work.

Answer 2

Run this statement:

SELECT 'rename table '||table_name||' to '||'jos'||substr(table_name,5)||';'
FROM information_schema.tables
WHERE table_name like 'jmla%'

This creates a script that will rename all the tables. Just copy & paste the output into your SQL client.

(You will need to change the || to MySQL's non-standard concatenation operator in case you are not running it in ANSI mode)