How to Change WordPress Table Prefix of mySQL Database

If you like to change the WordPress table prefix of the database on an existing WordPress website, you can use the following SQL query, where you have to update the header and the SET properties to your needs:

• database name
• oldprefix_
• newprefix_

This is the SQL query:

SET @database  = "databasename";
SET @oldprefix = "oldprefix_";
SET @newprefix = "newprefix_";
 
SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @oldprefix, @newprefix),
        ';'
    ) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

This query will create multiple SQL queries like

RENAME oldprefix_options to newprefix_options;
RENAME oldprefix_users to newprefix_users;

Copy these queries and execute them to rename all tables to the new ones.

After renaming all tables, you also need to replace some values in the table *_usermeta and *_options by using the queries below.

Don’t forget to update the boldly highlighted strings.

To replace the values in the *_usermeta table use this query:

UPDATE `newprefix_usermeta`
SET meta_key = REPLACE(meta_key, 'oldprefix_', 'newprefix_')
WHERE meta_key LIKE 'oldprefix_%';

To replace the values in the *_options table use this query:

UPDATE `newprefix_options` 
SET option_value = replace(option_value, 'oldprefix_', 'newprefix_') 
WHERE option_name LIKE 'oldprefix_%';

That’s all.

You can find a more in-depth technical article here:
How to rename the WordPress table prefix