Filed under drush, Planet Drupal

This is a post I’m digging up from an old blog of mine, but refreshed. I needed to remove the table prefix from an existing Drupal site. The site had tables in the form of drup_TABLENAME and needed to be in the standard Drupal format as TABLENAME. I wrote a quick php script which can be called from Drush.

The script does not delete any existing tables unless needed, and but will drop a table by the same name (without the prefix) if it exists before trying to rename it. The database I was working in was pretty dirty and had a fresh install mixed amongst the prefixed tables.

I’ve tested and ran it against the site using the following steps:

1. Run the file with drush

   drush php-script fix_prefix.php

2. Remove the $db_prefix from settings.php

3. Use drush to clear all the caches

   drush cc all

The script is also available as a gist

fix_prefix.php:

    <?php    // current table prefix to be removed    $prefix = "drup_";    // echo generated statments rather then run them    $pretend = FALSE;    /////////////////////////////////////////////////////////////////////    $table_list = db_query("SHOW TABLES");    $prefix = strtolower($prefix);    foreach ($table_list as $r) {      $r = (array)$r;      $table_old = strtolower(current($r));      // check for $prefix on this table      if(substr($table_old,0,strlen($prefix)) == $prefix) {        $table_new = substr($table_old, strlen($prefix));        // first drop $table_new incase it already exists        $clean_sql = "DROP TABLE IF EXISTS {$table_new}";        // rename prefix table to standard/nonprefix name        $rename_sql = "RENAME TABLE {$table_old} TO {$table_new}";        if($pretend) {          print $clean_sql."\n";          print $rename_sql."\n";        } else {          if(!db_query($clean_sql)) {            die("Aborting - $clean_sql \n");          }          if(!db_query($rename_sql)) {            die("Aborting - $rename_sql \n");          }        }      } else {        print "$table_old skipped \n";      }    }    print "\nDone \n\n";    ?>