Skip to content

Instantly share code, notes, and snippets.

@rmorse
Last active October 26, 2020 09:22
Show Gist options
  • Save rmorse/4106bdff98ff296d0057ceb24d7e8b21 to your computer and use it in GitHub Desktop.
Save rmorse/4106bdff98ff296d0057ceb24d7e8b21 to your computer and use it in GitHub Desktop.
Bulk change WordPress table prefix
<?php
// Warning: There is nothing safe about this script - don't use in production - make a backup. Always delete when finished.
/*
* Use this script to change the table prefix (renames the tables) in your WordPress install.
* Instructions:
* - Put this in a subdirectory from the root of your install - ie, create a new directory in the same folder as `wp-config.php`
and put this in it
* - Change the values `$old_prefix` and `$new_prefix` below, to match your requirements
* - Change the `$table_prefix` var in `wp-config.php` to your new prefix
* - Rename the option `wp_user_roles` (in `wp_options` table) to match your new prefix
*/
// the option `wp_user_roles` in the table `wp_options` needs to be renamed to match the new prefix - there are other `wp_`
// related options, but at this stage, `wp_user_roles` is the only one I can see that actually uses the DB prefix
// rather than a default `wp_`
require_once( '../wp-load.php' ); // sideload WP - use $wpdb to connect to the DB
global $wpdb;
//only change the following 2 values
$new_prefix = 'prefix_'; //the prefix you want
$old_prefix = 'wp_'; //the prefix you have currently
// $old_prefix = $wpdb->prefix; // or you can load the prefix from $wpdb directly
// get list of tables
$tables = $wpdb->get_results(' SHOW TABLES ' );
$old_prefix_length = strlen( $old_prefix );
foreach ($tables as $table) {
foreach ($table as $table_name){
//check table name starts with prefix
if ( 0 === strpos( $table_name, $old_prefix ) ) {
// replace the prefix
$new_name = sanitize_text_field ( $new_prefix . substr( $table_name, $old_prefix_length ) );
// now update the DB
// using $wpdb->prepare breaks this, perhaps rename not supported?
$update_result = $wpdb->get_results(
"
ALTER TABLE $table_name
RENAME TO $new_name
"
);
echo 'From: ' . esc_html( $table_name ) . ' | To:' . esc_html( $new_name ) . '<br />';
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment