Rename all tables and columns to lower case in MySQL?

You can achieve this with the help of INFORMATION_SCHEMA.COLUMNS. The syntax is as follows −

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS anyAliasName
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘yourDatabaseName’;

Now use the database which has two tables. The database name is as follows “bothinnodbandmyisam”. This database is having the following tables −

employee
student

The description of the employee table is as follows −

mysql> desc employee;

The following is the output. Let’s say we have the following columns in the employee table which are not in lowercase −

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| EmployeeId   | int(11)     |  YES |     | NULL    |       |
| EmployeeName | varchar(30) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

The description of the student table is as follows. The query is as follows −

mysql> desc student;

The following is the output. Let’s say we have the following columns in the student table which are not in lowercase −

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| StudentId   | int(11)     | YES  |     | NULL    |       |
| StudentName | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Here is the query to change column names of all tables to lowercase. The query is as follows −

mysql> SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
   -> LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS changeColumnNameToLower
   -> FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bothinnodbandmyisam';

The following is the output displaying the ALTER TABLE command that shows the updated column names −

+------------------------------------------------------------------------+
| changeColumnNameToLower                                                |
+------------------------------------------------------------------------+
| ALTER TABLE employee CHANGE `EmployeeId` `employeeid` int(11);         |
| ALTER TABLE employee CHANGE `EmployeeName` `employeename` varchar(30); |
| ALTER TABLE student CHANGE `StudentId` `studentid` int(11);            |
| ALTER TABLE student CHANGE `StudentName` `studentname` varchar(20);    |
+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Look at the above sample output, all column names have been changed in lowercase.

karthikeya Boyini

Updated on: 2019-07-30T22:30:25+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started