a MYSQL script to convert the column names to lowercase

Question

I am looking for a single MYSQL script to convert ALL column names in a database to lowercase in one go...

I have inherited a MYSQL database that has a lot of mixed case column names (150 tables with a strange naming convention) and I don't want to go through manually each table by table to do this.

Has anyone got such a script?

Thanks

Answer 1

In case anybody else wants this below is an example of the completed query, please test before you use.....

EDIT COMPLETED SOLUTION AS REQUESTED

SELECT CONCAT(
'ALTER TABLE ', table_name, 
' CHANGE ', column_name, ' ', 
LOWER(column_name), ' ', column_type, ' ', extra,
CASE WHEN IS_NULLABLE = 'YES' THEN  ' NULL' ELSE ' NOT NULL' END, ';') AS line
FROM information_schema.columns
WHERE table_schema = '<DBNAME>' 
AND data_type IN ('char', 'varchar','INT', 'TINYINT', 'datetime','text','double','decimal')
ORDER BY line;

HTH somebody in the future... BTW views are also scripted here so you may need to take them out of your final SQL code