Rename all tables and columns to lowercase in MySQL?

You can do 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 a database with two tables. The database name is as follows "bothinnodbandmyisam". The database has the following tables -

• employee
• student

employee table description is as follows -

mysql> desc employee;

The following is the output. Assume that the following columns in the employee table are not 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. Suppose the students table has the following columns which are not lowercase -

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

This is the query to change the 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 of the ALTER TABLE command showing 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)

Looking at the above example output, all the column names have been changed to lowercase.

The above is the detailed content of Rename all tables and columns to lowercase in MySQL?. For more information, please follow other related articles on the PHP Chinese website!