MySQL: how to find tables with column(s) that has Extra on update current

I am working on MariaDB V10.6, and just realized the following extra feature has been automatically added to a timestamp column in a table that I am working on.

on update current_timestamp()

With some help from Google, I was able to remove this unwanted extra from this table.

My question is, is there a way I can find all the table in the database that has this extra and remove them if they are not desired? Some sort of query that I can run against schema?

Thanks.

CodePudding user response：

This is documented behaviour https://mariadb.com/kb/en/timestamp/ ,to find

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'YOURSCHEMA' AND EXTRA ='on update current_timestamp()';

To modify

alter table T modify column yourcolumn timestamp default 0;

But do you really want such a default? And is timestamp a suitable datatype for your purposes?

https://dbfiddle.uk/lnPEBnXq