0

Is there a way to change all columns that have a specific datatype in a database?

I have some columns that their datatype is datetime(6) and if any is like that I would just want them to be datatime ... without the 6 fractional numbers. Is this possible to do in MySql without having to specify every column? Thanks

raygo
  • 1,348
  • 5
  • 18
  • 40
  • well you can use the modify function ..you can see more about it on http://stackoverflow.com/questions/3773480/how-do-i-alter-table-column-datatype-on-more-than-1-column – Avinash Babu Nov 06 '14 at 16:15
  • I want to do it in the whole database. – raygo Nov 06 '14 at 16:17
  • Then you would need to write a script that will crawl schemas of all tables and generate ALTER TABLE queries. – David162795 Nov 06 '14 at 16:27

1 Answers1

1

You must create text string with necessary statements and save it in somewhere (i don't have columns with datetime(6), but must be like that):

select 
concat('ALTER TABLE ',
        TABLE_SCHEMA,
        '.',
        table_name,
        ' CHANGE COLUMN ',
        Column_name,
        ' ',
        Column_name,
        ' DATETIME NULL DEFAULT NULL')
from
    INFORMATION_SCHEMA.COLUMNS
where
    table_schema = 'my_schema'
        and data_type = 'datetime'
    and character_maximum_length = 6 #may be numeric_precision, i don't know

after use exec statement, like this:

PREPARE stmt1 FROM  'ALTER TABLE ....';
EXECUTE stmt1
al_kash
  • 87
  • 4
  • I had to use datetime_precision and put a semicolon at the end of the query. But this pretty much worked like a charm! Thanks – raygo Nov 06 '14 at 18:19