Bulk change WordPress table prefix

change-prefix.php

<?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 />';
		}
    }
}