CODEWITHSUNDEEP
mysqlsql
Lloyd Banks
Lloyd Banks

Reputation: 36638

Replacing All Column and Table Names in MySQL Database

I have about 20 tables in my database where I use the word 'car' in many of the table names and the column names.

Is there a script or tool that will allow me to replace every instance of 'car' with 'vehicle'? I do not want to replace any of the table data itself.

Upvotes: 2

Views: 1162

Answers (1)

Jean-François Savard
Jean-François Savard

Reputation: 21004

You could generate the RENAME TABLE statements with a query, then simply copy and execute them.

select concat('rename table ', table_name, ' to ', substring_index(table_name, 'car', 1), 'vehicle',  substring_index(table_name, 'car', -1), ';')
from information_schema.tables
where table_schema = 'your_schema'
  and table_name like '%car%';

The substring-index will return what is before the first occurence of 'car' when given 1.

It will return what is after when given -1.

Upvotes: 2

Related Questions