Remove database prefix from existing Drupal site

Filed under 

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";

    ?>