Multiple Table Name Changes?

[gwmbox]

Hi

Is there a way to do multiple changes in the one DB, i.e. I have a DB that has 226 tables prefixed with AB and I want to chnage that to ab - i.e. lowercase.

I have had a look at www.mysql.com for rename tables etc but could not locate anything on doing multiple name changes

Thanks

Greg

[LordShryku]

I don't know that's there's one command to do all the alter tables you need to do, but this would be a fairly easy and quick script to write.

[gwmbox]

Thanks for the quick reply, any chnace you could write me up a quick script to start me off that I could then edit for my DB and try it out.

Thanks

Greg

[LordShryku]

Uhm...no. But I will give you a concept of what you need to do, and you can write it.

1) Connect
2) Select DB
3) Get table names
4) Loop through results, checking substring of table name
5) If condition applies, execute ALTER TABLE statement.

Everything you need is at
http://php.net/mysql and http://php.net/substr

[gwmbox]

Originally posted by LordShryku
Uhm...no. But I will give you a concept of what you need to do, and you can write it.

1) Connect
2) Select DB
3) Get table names
4) Loop through results, checking substring of table name
5) If condition applies, execute ALTER TABLE statement.

Everything you need is at
http://php.net/mysql and http://php.net/substr

Thats fair enough I have had a go

<?php
// connect to mysql
   $link = mysql_connect("localhost", "mysql_user", "mysql_pass")
       or die("Could not connect: " . mysql_error());
   print ("Connected successfully");

// make DBNAME the current db
mysql_select_db('DBNAME', $link) or die ('Can\'t use DBNAME : ' . mysql_error());

//get table names
$result = mysql_list_tables("DBNAME");

   for ($i = 0; $i < mysql_num_rows($result); $i++)
       printf ("Table: %s\n", mysql_tablename($result, $i));

   mysql_free_result($result);

function TableExists($tablename, $db) {

   // Get a list of tables contained within the database.
   $result = mysql_list_tables($db);
   $rcount = mysql_num_rows($result);

   // Check each in list for a match.
   for ($i=0;$i<$rcount;$i++) {
       if (mysql_tablename($result, $i)==$tablename) return true;
   }
   return false;
} 

   mysql_close($link);
?>

Which brings up all my tables 🙂

But I am not sure about item 4 - any pointers.

Thanks

Greg