1

I'm planning an upgrade to MySQL where DATETIME, TIMESTAMP, TIME and YEAR are potentially a problem. I'd like to query the database to find all uses of these datatypes. How can I accomplish this?

1
  • You plan migrate to MySQL from what? You want scan source database or target? and last - "usage" it is tables columns or insane in queries, procedures, triggers?
    – a_vlad
    Commented Mar 11, 2017 at 7:30

1 Answer 1

0

The following query will search for you usage of these datatypes across a database.

SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = "database"
      AND DATA_TYPE in ('DATETIME', 'TIME', 'TIMESTAMP',
                        'YEAR(2)', 'YEAR(4)', 'YEAR');
1
  • Grab TABLE_NAME, too.
    – Rick James
    Commented Mar 13, 2017 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.