Sql script to modify column data types

Does anyone know a script to alter the data type of a column in a table which is part of number of databases? e.g.

I have a user table in 20 different databases with same columns. I want to开发者_StackOverflow社区 change the datatype of one of the columns from that table.

You can use the information_schema to generate DDL commands to modify the columns.

For example, if all of the tables are named "user" (and there are no "user" tables in other databases that you DO NOT want to change), and your column is named "change_me", and you want to make it an unsigned int that is never null, you could do something like this:

select concat('ALTER TABLE ',table_schema,
'.',table_name,
' MODIFY COLUMN ',column_name,
' INT UNSIGNED NOT NULL;') as sql_stmt 
into outfile '/tmp/modify_columns.sql'
from information_schema.columns 
where table_name = 'user' 
and column_name = 'change_me';

\. /tmp/modify_columns.sql

Also remember that many times, the column must be empty to change data types and will depend upon DBMS. Please specify from and to what datatype. Alternatives:

desc foo;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
BAR1                                    NUMBER      

-- this will not work:
alter table foo modify ( bar1 varchar(255) );    


--instead
alter table foo add ( bar2 varchar(255));

-- assume the DB will auto change type:
update foo set bar2=bar1;

alter table foo drop (bar1);
alter table foo rename column bar2 to bar1;