MYSQL find if a default value has been set for a table (DateTime Specifically)

default valuedistributed-databasesMySQL

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. The problem is that each is on their own timezone and this creates a mess of disentangling the various timezones, translating them to UTC, and then re-translating them back to the users timezone.

This database has some 140+ tables and I'd like something more efficient than searching manually by using SHOW CREATE on each table, plus I'd like to be able to check when I'm done that everything was converted correctly .

Any ideas on a query that would do this?

To be clear, I'm looking for a query to SELECT the rows that I need, I can figure out a INSERT... SELECT statement from that.

Best Answer

Using @a1ex07 answer, I used this query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE (table_schema, DATA_TYPE) = ('my_schema', 'timestamp') AND COLUMN_DEFAULT IS NOT NULL;

That showed that many were using CURRENT_TIMESTAMP instead of UTC_TIMESTAMP.

Related Question