MySQL - How to alter column datatype in all databases?

Admin 1
Viewed 43

On my remote server, I need to update a column in a table in all databases. How can I do this all at once? I have over than hundred databases with the same table.

ALTER TABLE tablename MODIFY columnname VARCHAR(255);
ddl command-line mysql
1 Answers

You can write a SQL query to emit a script that does this. The query looks at the information_schema table describing columns in your server, and generates a sequence of ALTER queries. You then run those queries. 

SELECT CONCAT(' ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
              '` MODIFY `',COLUMN_NAME,'` VARCHAR(255);') ddl 
  FROM information_schema.`COLUMNS` 
 WHERE TABLE_NAME = 'tablename' 
   AND COLUMN_NAME IN ('columnname')

There isn't any oneliner to do ALTER TABLE *.tablename or anything like that.

user_2056080
Related