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?

Neighborly

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.

Axon

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)

With the output of this statement, append

AND table_schema = 'your_joomla_db'

So it reads

rename table ...(output produced)
...
WHERE/AND table_schema = 'your_joomla_db'

Table schema is mysql's internal label for database name, ensure you include it.

Annadiana

Make sure you also filter on the database you want to apply this on, otherwise this will produce RENAME queries across all databases including other Joomla installations you may have: AND TABLE_SCHEMA = 'your_joomla_db' – Thymus

Answer 3

RENAME TABLE jmla_whatever to jos_whatever;

You'll have to write a script to cover all your tables - you can populate your script with the output of show tables. See http://dev.mysql.com/doc/refman/5.0/en/rename-table.html for details.

Perky

Answer 4

1. Export using phpmyadmin to .sql file
2. Use any text editor (I prefer vim, work excellent on large files) with function "find and replace" to open file
3. Do "find and replace", putting your actual prefix in find box, and updated in replace box
4. Import file using phpmyadmin.

Remember to drop old database before importing by phpmyadmin. This may be done, checking suitable options during export.

Spidery

And what happens if one of the names of the rows has "jmla_" in it? Or worse, if the data has that somewhere in there? – Blowtorch

1. I never saw this case in practice 2. My solution made another good thing, changing point to non-exsist database. – Interferometer

The point is, when there is a better solution out there, it's not worth the risk of doing a simple find and replace. The solution can lead to issues that are hard to debug in the future. – Blowtorch

I know only this solution. If any better (fast and easy) exist, you are right. Thanks. – Interferometer

Answer 5

Have you considered using Akeeba Admin Tools Component for Joomla! and then using the Database Prefix Editor to change table prefixes, works really well. Akeeba Admin tools can be used to secure your Joomla! installation

Akeeba can be obtained here

Procurance

Answer 6

If you have multiple tables with the same name in different databases, otherwise known as table_schemas, ensure you are connected to the one you want to rename tables inside, or use a where clause on the renames to ensure your intended target database.tables are renamed.

USE target_db_name;
SELECT concat (
  'rename table ', table_name, ' to ',
    'new_prefix_',
    substr(table_name, 11),
  ';')
FROM information_schema.tables
WHERE table_name like 'old_prefix_%'
  and table_schema='target_db_name';

Aesthetic