How add table name prefix to all table after Magento installation

I need to add table prefix for all the database table.

I know that I can get this option while installing the Magento but I already installed my magento and I don't want to change the database, I just need to add the prefix for all the tables.

I checked lot for this but I didn't get perfect answer for this.

Can anyone help me to do this,

How can I add prefix to all the tables without any data loss?

The way I know is to use the following command either one by one:

RENAME TABLE tb1 TO my_prefix_tb1;


or

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


as described here

and then edit the app/etc/local.xml and find the tag with name <table_prefix> and update it accordingly <table_prefix><![CDATA[my_prefix_]]></table_prefix>, and save it.

You have to run these queries directly in the database so please please take a backup first or try on staging.

• @Charlie, please accept the answer if it sorted your issue. – MTM Jun 2 '15 at 8:09

you could delete local.xml (or rename to local.xml.bak) in app->etc which will trigger running through the installer again including the database connection section of the installer where you can enter the prefix name.