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 change the datatype of one of the columns from that table.
Add a comment
|
2 Answers
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
Comments
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;
