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

You can solve this task by building a script, starting with the output from this statement:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'dbname';
ORDER BY table_name

Details about this feature can be found here "MYSQL::The INFORMATION_SCHEMA COLUMNS Table"

Then you can use the ALTER TABLE .. CHANGE feature to change the name of the columns

e.g.

ALTER TABLE mytable CHANGE old_name new_name varchar(5);

See also "MYSQL::ALTER TABLE Syntax"

Different datatype have different requirements so you need the UNIONs:

SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(character_maximum_length)||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'CHAR', 'VARCHAR' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(numeric_precision)||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'INTEGER' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(numeric_precision)||','||CHAR(numeric_scale)|');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'FLOAT' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'DATE' )
    ORDER BY table_name