12

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.

Community
  • 1
  • 1
jyoseph
  • 5,435
  • 9
  • 45
  • 64

6 Answers6

17

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}'
Fırat Küçük
  • 5,613
  • 2
  • 50
  • 53
user194076
  • 8,787
  • 23
  • 94
  • 154
  • Doesn't appear to work; command appears to have succeeded, but a `describe **tablename**` shows capitalized columns. – aendra Jan 29 '12 at 00:06
  • Granted, maybe I just need to restart MySQL. I'm doing this on a shared host, so not sure if that is or isn't the case. – aendra Jan 29 '12 at 00:12
  • It didn't work form me initially, because TextEdit on Mac messes around with the single quotes. They were changed to something else during copy and paste. – demisx Apr 17 '15 at 15:55
  • i dont think that this query accounts for primary key columns with auto increment, for example. The 'COLUMN_TYPE' does not include auto increment or default values fo rthe columns. – omarjebari Aug 13 '17 at 10:41
  • WARNING: All columns that has auto-increment will lose auto-increment – Mohamad Hamouday Jan 24 '21 at 12:38
11

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';
Vitaly Olegovitch
  • 3,509
  • 6
  • 33
  • 49
2

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.

Dave Benson
  • 56
  • 4
  • This works well but i needed to add the DISTINCT keyword to the select otherwise i got duplicate records returned. – omarjebari Aug 13 '17 at 10:55
2

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);
Jaanus
  • 16,161
  • 49
  • 147
  • 202
1

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'
omarjebari
  • 4,861
  • 3
  • 34
  • 32
0

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
Mohamad Hamouday
  • 2,070
  • 23
  • 20