If you are a webmaster and you have multiple sites under one web hosting account then you probably are using prefixes at your database tables, especially when your web host allows only one database per account. The prefix at a table is a good way to have two or more sites of the same kind installed, like 2 wordpress blogs or other cms (content management systems). But what if you move the one blog or website to another hosting account and you want now the database tables to have clean and distinctive names? Well there is a solution.

Renaming the tables using SQL

Paste the following code (replacing the variables to match your situation) in the SQL box of phpMyAdmin (web based MySQL administration panel):

GROUP_CONCAT(CONCAT("RENAME TABLE ", table_name, " TO ", SUBSTRING(table_name, the_number_of_characters_to_strip)) SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "YOURDATABASE" AND table_name LIKE "yourprefix_%";

The output of the above code will result a long string with commands of renaming the tables.

If the output of the above code is truncated and doesn’t contain all the tables that got to be renamed then use the following command before the above:

set group_concat_max_len=10000000;

You copy and paste that result at the SQL box of phpMyAdmin and hit execute! And then all the tabes are renamed without the prefix.

Note: Don’t forget to change your configuration file of your website, or else your website won’t work.

Did you like this page ? Let your best friend know !

Leave a Reply

Your email address will not be published. Required fields are marked *