My provider installed to my site Drupal CMS. Now I need copy all my data from old site. I have tables without prefixes in my old DB, but in new DB all tables have "dp_[table_name]" prefix.
A:
write a script that will run RENAME TABLE for each table.
SELECT
GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM
`TABLES` WHERE `TABLE_SCHEMA` = "test";
where "test" is expected database name
after this you can long query that will add prefixes if you execute it ;-)
zerkms
2010-03-17 21:44:53
A:
You can simply dump the database, open the dump with a text editor, replace all occurrences of "CREATE TABLE " with "CREATE TABLE dp_" and restore the database. It takes a couple of minutes to do.
Tomislav Nakic-Alfirevic
2010-03-17 22:12:57
until the database is less than couple of gigabytes ;-)
zerkms
2010-03-17 22:30:27
I'm game. :) For databases larger than a GB, use a query tool (SQL Workbench, for example), select all the tables in the table list, copy the contents into column A of a spreadsheet (in my case, Calc) and put the following formula in column B: ="rename table " " Paste the formula from cell B1 into every other B column cell and the rename script appears in column B!
Tomislav Nakic-Alfirevic
2010-03-17 23:05:40
as i said in my answer - an appropriate way to rename is to use RENAME TABLE (+ information_shema to retrieve tables names)
zerkms
2010-03-18 01:39:22