Alex Rieker Alex Rieker - 4 months ago 14
SQL Question

Replace space with underscore in all table names in database

I'm trying to replace all space (' ') with a underscore in all the table names in a database using PhpMyAdmin.

Ex:
Table 1 --> Table_1

I was wondering if this is possible. I know it's possible to do this for columns but I was wondering if someone could write me something for tables. I don't use PhpMyAdmin very often, but I installed it in this case becuase it works easily.

Answer

You could run a query like this to generate the SQL that will perform a rename:

SELECT CONCAT('RENAME TABLE ', table_name, ' TO ' , REPLACE(table_name, ' ', '_') , ';') 
FROM information_schema.tables 
WHERE table_schema = 'your_schema_name';

Remember to substitute your_schema_name for whatever your database is called.

To run the query in phpMyAdmin you can click the SQL tab at the top of the window, then paste the SQL into the box. The result of the above query will be SQL generated based off of the existing table names. Just copy this resulting SQL back into the textbox and run it again to perform the renames.