CODEWITHSUNDEEP
mysqlsqlstored-procedures
walter
walter

Reputation: 309

Rename column for all tables in mysql database

I need to rename column for all tables in my databse. I could get list of columns using this query:

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME= <Column Name>;

But how actually I could rename it as simple as possible and do not write 

ALTER TABLE <Table Name >RENAME COLUMN <Old name> to <New Name>;

for each table.

I've tried to write a procedure:

DELIMITER $$
     DROP PROCEDURE IF EXISTS  renameColumn $$
     CREATE PROCEDURE renameColumn(IN oldName tinytext, IN newName tinytext)
     BEGIN
       DECLARE @name VARCHAR(255);
       DECLARE exit_loop BOOLEAN;         
       DECLARE tableName_cursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=oldName;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
       OPEN tableName_cursor;
       rename_loop: LOOP
        FETCH  tableName_cursor @name;
        ALTER TABLE @name RENAME COLUMN oldName to newName;
         IF exit_loop THEN
             LEAVE rename_loop;
         END IF;
       END LOOP rename_loop;
     END $$
     DELIMITER;

But I have the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@name VARCHAR(255);

DECLARE exit_loop BOOLEAN;

DECLARE ta' at line 3

Could you please help me to resolve this issue

Upvotes: 2

Views: 2298

Answers (2)

Ishan Chikhalkar
Ishan Chikhalkar

Reputation: 1

Try the following solution hope it will help

DECLARE @name VARCHAR(50) -- database name 

DECLARE db_cursor CURSOR FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME= 'OldColumn';

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      set @name = @name + '.OldColumn'
      EXEC sp_rename  @name, 'NewColumn', 'COLUMN';
      print(@name)
      FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

Upvotes: 0

Parvinder Singh
Parvinder Singh

Reputation: 163

You can try this.

SELECT CONCAT(
    'ALTER TABLE ', TABLE_NAME,
    ' RENAME COLUMN ', COLUMN_NAME,
    ' NEW_', COLUMN_NAME,
    ';') AS rename_script 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_SCHEMA = 'your_db'

Upvotes: 2

Related Questions