How do I find all uses of a data type in a MySQL database?

Question

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?

Answer 1

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');