Just call the following function but make sure to change new_prefix with whatever table prefix you want to add and also check whether config file path is correct at your side, i have used find_wp_config_path() function to find it but sometime it may return null.
function find_wp_config_path() {
$dir = dirname(__FILE__);
do {
if( file_exists($dir."/wp-config.php") ) {
return $dir;
}
} while( $dir = realpath("$dir/..") );
return null;
}
function change_table_prefix() {
global $wpdb, $table_prefix;
$old_table_prefix = $table_prefix;
$old_prefix_length = strlen( $old_table_prefix );
$table_new_prefix = 'new_prefix';
/* Config file path */
$path = find_wp_config_path();
$error = $wpdb->set_prefix( $table_new_prefix );
if ( is_wp_error( $error ) )
wp_die( '<strong style="color: #ff0000;">ERROR</strong>: table prefix can only contain numbers, letters, and underscores.' );
//Get the table resource
$result = mysql_list_tables(DB_NAME);
//Count the number of tables
$num_rows = mysql_num_rows( $result );
$table_count = 0;
//Rename all the tables name
for ($i = 0; $i < $num_rows; $i++) {
//Get table name with old prefix
$table_old_name = mysql_tablename($result, $i);
if ( strpos( $table_old_name, $old_table_prefix ) === 0 ) {
//Get table name with new prefix
$table_new_name = $table_new_prefix . substr( $table_old_name, $old_prefix_length );
//Write query to rename tables name
// $sql = "RENAME TABLE $table_old_name TO $table_new_name";
$sql = "RENAME TABLE %s TO %s";
//Execute the query
if ( false === $wpdb->query($wpdb->prepare($sql, $table_old_name, $table_new_name)) ) {
$error = 1;
echo "<p class="error">", $table_old_name, " table name not updated.</p>";
} else {
//echo "<p class="success">$table_old_name table name updated to $table_new_name.</p>";
$table_count++;
}
} else {
continue;
}
}
if ( @$error == 1 ) {
echo "<p class="error">Please change the above tables prefix to ", $table_new_prefix, " manualy.</p>";
} else {
echo "<p class="success">", $table_count, " tables prefix updated successfully.</p>";
}
//Update the wp-config.php file
$configFile = file($path);
foreach ($configFile as $line_num => $line) {
switch (substr($line,0,16)) {
case '$table_prefix =':
$configFile[$line_num] = str_replace($old_table_prefix, $table_new_prefix, $line);
break;
}
}
//making the the config readable to change the prefix
@chmod($path, 0777);
if ( is_writeable($path) ) {
$handle = fopen($path, 'w');
foreach( $configFile as $line ) {
fwrite($handle, $line);
}
fclose($handle);
echo '<p class="success">wp-config.php file updated successfully.</p>';
} else {
echo "<p class="error">File Not Writeable: Please open wp-config.php file in your favurite editor and search
for variable", $table_prefix, " and assign ", $table_new_prefix, " to the same variable.";
}
//Create query to update option table
$update_option_table_query = "UPDATE " . $table_new_prefix . "options
SET option_name="" . $table_new_prefix . "user_roles"
WHERE option_name="" . $old_table_prefix . "user_roles"
LIMIT 1";
//Execute the update query to update option table user_roles row
if ( false === $wpdb->query($update_option_table_query) ) {
echo "<p class="error">Changing value: ",
$old_table_prefix,
"user_roles in table ",
$table_new_prefix,
"options to ",
$table_new_prefix,
"user_roles</p>";
echo "<p class="error">End of updating options table data with above error.</p>";
} else {
echo "<p class="success">Updated options table data successfully.</p>";
}
//Create query to update user_meta table
$custom_sql = "SELECT user_id, meta_key
FROM " . $table_new_prefix . "usermeta
WHERE meta_key
LIKE '" . $old_table_prefix . "%'";
$meta_keys = $wpdb->get_results( $custom_sql );
//Update all the meta_key field value which having the old table prefix in user_meta table
foreach ($meta_keys as $meta_key ) {
//Create new meta key
$new_meta_key = $table_new_prefix . substr( $meta_key->meta_key, $old_prefix_length );
$update_user_meta_sql = "UPDATE " . $table_new_prefix . "usermeta
SET meta_key='" . $new_meta_key . "'
WHERE meta_key='" . $meta_key->meta_key . "'
AND user_id=" . $meta_key->user_id;
$wpdb->query($update_user_meta_sql);
}
echo "<p class="success">Updated usermeta table data successfully.</p>";
}
or you can directly use this plugin http://wordpress.org/plugins/change-table-prefix