CODEWITHSUNDEEP
mysql
Jonah Katz
Jonah Katz

Reputation: 5288

Lower case all the table names in a database?

I have a mysql database with about 8 tables that all begin with a capital letter. Any quick way to lowercase them all? Or even one by one... if i try this, RENAME TABLE Contacts TO contacts it says ERROR 1050 (42S01): Table 'contacts' already exists

Upvotes: 5

Views: 1701

Answers (3)

user1531135
user1531135

Reputation:

I'm sorry for bringing back an old post. But I had problems with this aswell. I made this function to automate the above. I didn't help in my case, but might help other people. That's why I'm posting it here.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `lowercasetables`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tempname varchar(255);
    DECLARE backupname varchar(255);
    DECLARE sqlst varchar(5000);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES where table_schema = schema();

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur1;

    REPEAT
        FETCH cur1 INTO tempname;        
        SET backupname = concat(tempname,'_BACKUP'); 

        SET @sqlst = CONCAT(CONCAT('RENAME TABLE ', tempname), CONCAT(' TO ', backupname));
        PREPARE stmt1 FROM @sqlst;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

        SET @sqlst = CONCAT(CONCAT('RENAME TABLE ', backupname), CONCAT(' TO ', LOWER(tempname)));
        PREPARE stmt2 FROM @sqlst;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;

    UNTIL done END REPEAT;

    CLOSE cur1;

END

Upvotes: 0

Nikoloff
Nikoloff

Reputation: 4160

Use two renames - first to a temp name and then to the lowercased:

RENAME TABLE Contacts TO contacts_

and then 

RENAME TABLE contacts_ TO contacts

Of course, you should be careful not to try using an already existing table name, but if you initially had tables 'Contacts' and 'contacts_' I'd say you have way more serious problems than the case.

Upvotes: 4

Adel Boutros
Adel Boutros

Reputation: 10285

By checking this link, you have to:

RENAME TABLE tbl_name TO new_tbl_name

BUT : The new_tbl_name must NOT be used by another table as the name must be unique

Upvotes: 0

Related Questions