Batch-renaming MySQL tables

This post was written on 2006/06/25 at 20:18:43 by Horst Gutmann

Ever felt the need to batch-rename quite a few tables in a MySQL database? Sadly phpMyAdmin doesn't offer renaming of all the tables in a specific database. So I first started renaming them manually using the commandline interface but got soon quite tired of the whole process.

A quite ugly but IMO pragmatic workaround for this limitation is to build a small query file holding all the RENAME TABLE statements you want. To generate this query listing first of all get a list of all the tables you want to rename.

Since I'm lazy all I want to do is a show tables; and then copy this output to some variable inside of some script. Well, and then this script should generate me the statement list I will execute with the MySQL commandline client: 

OLD_PREFIX = ""
NEW_PREFIX = "newprefix_"
data=<<-EOD
| aggregator_category_item          |
| aggregator_feed                   |
| aggregator_item                   |
| search_total                      |
| sequences                         |
EOD
tables = []
data.each_line do |line|
  if line=~/(#{OLD_PREFIX}\w+)/
    puts "RENAME TABLE #{$1} TO #{NEW_PREFIX}#{$1};"
  end
end

The data variable here holds the output I got from show tables;. The output of this script will look like this: 

RENAME TABLE aggregator_category_item TO newprefix_aggregator_category_item;
RENAME TABLE aggregator_feed TO newprefix_aggregator_feed;
RENAME TABLE aggregator_item TO newprefix_aggregator_item;
RENAME TABLE search_total TO newprefix_search_total;
RENAME TABLE sequences TO newprefix_sequences;

Variable regex are ugly ... but sometimes they make things sooooo easy ;)

Comments:

  • Anonymous (Guest)

    Excuse my ignorance, but what kind of script is this? As in how do you run this?

    I'd like to see this script work, since it's exactly what i'm looking for.

    July 25, 2006, 7:16 p.m.

  • zerok

    It's a [HTML_REMOVED]Ruby[HTML_REMOVED] script. You simply edit the OLD_PREFIX and NEW_PREFIX constants as well as the data and put all into a file and run it with ruby filename.

    Sorry for the late reply, but I completely overlooked your comment :-(

    Aug. 8, 2006, 10:15 p.m.

*'d input fields are required.