1

In MySQL, how to change all columns names of all tables to remove the string "_euro" from columns names?

I just could find a way to search tables having some columns containing "_euro" in their names:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE "%_euro"
    AND TABLE_SCHEMA='my_database'

For example, for the column named price_total_euro I want to rename it as price_total

London Smith
  • 1,622
  • 2
  • 18
  • 39
  • You could create a script with contents like `ALTER table RENAME COLUMN price_total_euro TO price_total;` (and repeat that for every column found. – Luuk Sep 06 '20 at 16:22

3 Answers3

2

Create a script with the following SQL:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," RENAME COLUMN ",COLUMN_NAME," TO ",REPLACE(COLUMN_NAME,"_euro",""),"; ")
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE "%_euro"

output will be, multiple lines which look like this:

ALTER TABLE test.t1_euro RENAME COLUMN t1_euro TO t1;

Review the script, and execute it on your database (after making a backup....)

Luuk
  • 12,245
  • 5
  • 22
  • 33
0

put your select into a stored procedure and make a cursor and loop on all and do the alter operation

you can find example for a procedure here : https://stackoverflow.com/questions/15786240/mysql-create-stored-procedure-syntax-with-delimiter#:~:text=Getting%20started%20with%20stored%20procedure%20syntax%20in%20MySQL%3A,Why%20didn%27t%20this%20work%3F%20...%20More%20items...%20

mshahien
  • 63
  • 2
  • 9
0

if your using python with MySQL Connector you could place the column headers into a list and then run it through a for loop?

for name in table_names:
    if name.endswith("_euro"):
        new_name = name.replace("_euro", "")
IdrisSan
  • 1
  • 1