Magento change database table prefix

Question

I accidentally have an Magento installation without an underscore for the table prefix. Is there an option to change this automatically? I don't like to alter 337 tables by hand :-)

I tried this solution but that doesn't seem to work.

Michael

Did the selected answer work for you? What version of Magento? — Dan, Mar 06 '15 at 17:13

score 2 · Accepted Answer · answered Feb 11 '13 at 18:56

2

You can create the sql to rename all tables with this select:

SELECT 'rename table '||table_name||' to '||'newprefix'||table_name||';'
FROM information_schema.tables

answered Feb 11 '13 at 18:56

Tobias

1,692
12
21

Or use `SELECT CONCAT('rename table ', table_name , ' to ', 'newprefix_', table_name, ';') FROM information_schema.tables` for [better compatibility](http://stackoverflow.com/a/8212239/1402846). – Pang Sep 24 '14 at 03:02

score 0 · Answer 2 · edited Sep 24 '14 at 02:53

<?php
$database_host="localhost";
$database_user="root";
$database_password="";
$magento_database="test1";
$table_prefix="magtest_"; 
?>
<?php
$db=mysql_connect($database_host,$database_user,$database_password);
mysql_select_db($magento_database);
$query="SHOW TABLES";
$result=mysql_query($query) or die('Err');
while($row=mysql_fetch_array($result)){$old_table=$row[0];
if(preg_match('/'.$table_prefix.'/',$old_table)){echo"Table $old_table already done<br/>\n";continue;} 
$new_table=$table_prefix.$old_table;echo"Renaming $old_table to $new_table<br/>\n";
$query="RENAME TABLE `$old_table`  TO `$new_table`";mysql_query($query);} 
?>

Steps:

take database backup.
Create a File Named "rename_table_prefix.php" and placed at root directory.
Paste the above Script in it.
run the file http:www.domain.com/magento/rename_table_prefix.php
All tables will be renamed.
go to app/etc/local.xml
modify the following line as it is
you're done.

score 0 · Answer 3 · answered Jun 18 '15 at 08:01

I run this php script for change Magento DB Table prefix

// mege_rename_table_prefix.php
//New Prefix Name
$table_prefix = "test_";
//Magento Database Backup php script
    error_reporting(E_ALL ^ E_NOTICE);
    ini_set('display_errors', 1);
    ini_set('memory_limit', '1512M');
    // Get Magento Application
    require_once 'app/Mage.php';
    Mage::app();
  // Mage::app('default');
   //Mage::app('main');
    // get Magento config
    $config  = Mage::getConfig()->getResourceConnectionConfig("default_setup");
    $dbinfo = array(
        "host" => $config->host,
        "user" => $config->username,
        "pass" => $config->password,
        "dbname" => $config->dbname
    );
    // Database Config
    $db_host = $dbinfo["host"];
    $db_user = $dbinfo["user"];
    $db_pass = $dbinfo["pass"];
    $db_name = $dbinfo["dbname"];
//conect db
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
$query = "SHOW TABLES";
$result = mysql_query($query) or die('Err');
while($row = mysql_fetch_array($result)) {
    $old_table = $row[0];
    if(preg_match('/'.$table_prefix.'/', $old_table)) {
        echo "Table $old_table already done<br/>\n";
        continue;
    }
    $new_table = $table_prefix.$old_table;
    echo "Renaming $old_table to $new_table<br/>\n";
    $query = "RENAME TABLE `$old_table`  TO `$new_table`";
    mysql_query($query);
    }

score 0 · Answer 4 · answered Nov 15 '19 at 11:50

You can simply change that in PHPMyAdmin.

Click/open the database.

Click Structure at the top bar.
This will display all your tables. Note the existing prefix.

Scroll to the bottom, to the last table.

Click "Check all".
This will check all tables.

Click the drop down menu just next to it - the one with the default value "with selected".

Select "Replace table prefix:"
This will bring you to a new page with two text inputs.

Fill in your existing prefix, e.g. "oldPrefi_". Don't forget the underscore.

Fill in your new prefix, e.g. "newPrefi_". Don't forget the underscore.

Finally, click submit.

You will be redirected to the table list with the new prefix.

stevensagaar · Answer 5 · 2020-01-07T08:04:19.920

Step 1 - Change table_prefix in env.php file as shown in the screenshot below

Step 2 - Run the following query to get ALTER statements to add prefix to your existing tables -:

SELECT Concat('ALTER TABLE ', TABLE_NAME, ' RENAME TO qa_',
TABLE_NAME, ';') FROM information_schema.tables WHERE table_schema =
'DB_NAME'

Step 3 - Upgrade Magento 2 DB by running the following command on your Magento root folder

php bin/magento setup:upgrade

In case something goes wrong, you can remove table_prefix from env.php and run the following query on your DB instance to rename the tables back to original

SELECT Concat('ALTER TABLE  ', TABLE_NAME ,  '  RENAME TO ',
replace(TABLE_NAME,'qa_',''), ';') FROM information_schema.tables
WHERE table_schema = 'magento_2'

Hope it helps someone!

score 0 · Answer 6 · answered Dec 30 '20 at 13:53

Steps to fix that issue (100% it will work , I have the same issue and I fixed that)

Above solution mostly not work because you can UPDATE the table name , But the FORIEGN key mapping will cause the problem. So below is the best way to fix that.

Export whole DB (Backup)
Remove PREFIX word from whole file.
Remove the existing DB and import the Updated DB.
Remove PREFIX from env.php file

Magento change database table prefix

6 Answers6