Mysql - Rename all tables and columns to lower case?

I recently transferred a database from a 开发者_如何学运维windows box to a linux box. The tables are mixed between lower and upper case names. I need a way to rename all tables and columns to lowercase. Is that possible?

I see in this SO answer it's possible for tables, but have not found anything that deals with column names.

You can try to do exact same thing with Information_Schema.Columns table

EDIT: Something like

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';')
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{your schema name}'

You can use this script proposed by Anders Eriksson on MySQL Website:

 select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') from information_schema.tables where table_schema = 'your_schema_name';

Maybe used builtin functions LOWER() UPPER(). http://www.sqlinfo.net/mysql/mysql_function_upper_lower.php

alter table [table name] change [old column name] [new column name] varchar (50);

The above solutions were not complete enough for me. They dropped column attributes like auto_increment, default values, and Not Null. Additionally, I did not want to change columns in information_schema and mysql.

Warning: I did not research all column attribute combinations. Just the set that allowed me to convert my database. There could be more constants like CURRENT_TIMESTAMP in your database.

I used a few variations of the below to determine what was in my database.

SELECT distinct COLUMN_DEFAULT FROM information_schema.columns

This is the solution that worked for me:

select 
CONCAT('ALTER TABLE ', '`', t.TABLE_NAME, '`', ' CHANGE `', c.COLUMN_NAME, '`',
    ' `', LOWER(`COLUMN_NAME`), '` ', COLUMN_TYPE,
IF (IS_NULLABLE = "YES", ' ', ' NOT NULL'), 
IF (IS_NULLABLE = "YES",
    IF (COLUMN_DEFAULT IS NULL, 'DEFAULT NULL', IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', ' DEFAULT CURRENT_TIMESTAMP', CONCAT(' DEFAULT ', '''', COLUMN_DEFAULT, ''''))),
    IF (COLUMN_DEFAULT IS NOT NULL, IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', ' DEFAULT CURRENT_TIMESTAMP', CONCAT(' DEFAULT ', '''', COLUMN_DEFAULT, '''') ), '') ) ,
' ', EXTRA, ';')
from 
information_schema.tables t
JOIN 
information_schema.columns c
ON (c.table_name = t.table_name)
WHERE t.table_type = 'BASE TABLE'
AND t.table_schema not in ('information_schema', 'mysql')
INTO OUT FILE '/tmp/ColumnsToLowerCase.sql'

Time saving notes: To output to a file you have to login to the database with sufficient rights. I have root access so I logged in as root, but cut and past of the results works too if you have to.

OK i have modified Dave Benson's answer to add the DISTINCT keyword (to prevent duplicate records being returned) and also to check for nullable timestamp columns.

SELECT DISTINCT
    CONCAT('ALTER TABLE ', '`', t.TABLE_NAME, '`', ' CHANGE `', c.COLUMN_NAME, '`', ' `', LOWER(`COLUMN_NAME`), '` ', COLUMN_TYPE,
IF (IS_NULLABLE = "YES", ' ', ' NOT NULL'), IF (IS_NULLABLE = "YES", IF (COLUMN_DEFAULT IS NULL, IF (COLUMN_TYPE = 'TIMESTAMP', 'NULL DEFAULT NULL', 'DEFAULT NULL'), IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', ' DEFAULT CURRENT_TIMESTAMP', CONCAT(' DEFAULT ', '''', COLUMN_DEFAULT, ''''))), IF (COLUMN_DEFAULT IS NOT NULL,IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', ' DEFAULT CURRENT_TIMESTAMP', CONCAT(' DEFAULT ', '''', COLUMN_DEFAULT, '''')),'')), ' ', EXTRA, ';')
FROM information_schema.tables t
JOIN information_schema.columns c ON (c.table_name = t.table_name)
WHERE t.table_type = 'BASE TABLE'
AND c.table_schema not in ('information_schema', 'mysql')
INTO OUT FILE '/tmp/ColumnsToLowerCase.sql'

A better version of @Dave Benson, which get just your database tables and fix Current_timestamp

select
CONCAT('ALTER TABLE ', '`', t.TABLE_NAME, '`', ' CHANGE `', c.COLUMN_NAME, '`',
    ' `', LOWER(`COLUMN_NAME`), '` ', COLUMN_TYPE,
IF (IS_NULLABLE = "YES", ' ', ' NOT NULL'),
IF (IS_NULLABLE = "YES",
    IF (COLUMN_DEFAULT IS NULL, 'NULL DEFAULT NULL', IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', 'NULL DEFAULT CURRENT_TIMESTAMP', CONCAT(' NULL DEFAULT ', '''', COLUMN_DEFAULT, ''''))),
    IF (COLUMN_DEFAULT IS NOT NULL, IF (COLUMN_DEFAULT = 'CURRENT_TIMESTAMP', ' DEFAULT CURRENT_TIMESTAMP', CONCAT(' DEFAULT ', '''', COLUMN_DEFAULT, '''') ), '') ) ,
' ', EXTRA, ';')
from
information_schema.tables t
JOIN
information_schema.columns c
USING(`table_name`, `table_schema`)
WHERE t.table_schema = 'YOUR_DATABASE_NAME'
ORDER BY `TABLE_NAME` ASC