How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL I am using MySQL v.5.7.x.
In a database with 161 tables, one of the them has the following structure:

CREATE TABLE `prefix_coupon` (
`coupon_id` int (11) NOT NULL AUTO_INCREMENT,
`name` varchar (128) NOT NULL,
`code` varchar (10) NOT NULL,
`type` char (1) NOT NULL,
`discount 'decimal (15,4) NOT NULL,
`logged` tinyint (1) NOT NULL,
`shipping` tinyint (1) NOT NULL,
`total` decimal (15,4) NOT NULL,
`date_start` date NOT NULL DEFAULT '0000-00-00',
`date_end` date NOT NULL DEFAULT '0000-00-00',
`uses_total` int (11) NOT NULL,
`uses_customer` varchar (11) NOT NULL,
`status` tinyint (1) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`coupon_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8


See the columns date_start, date_end and their structure date NOT NULL DEFAULT '0000-00-00'?

Is there a way to query database tables for

date NOT NULL DEFAULT '0000-00-00'

and ALTER them

ALTER TABLE prefix_coupon CHANGE date_start date_start DATE NULL DEFAULT NULL, CHANGE date_end date_end DATE NULL DEFAULT NULL;
You can find that information in the INFORMATION_SCHEMA:

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT 
from information_schema.columns 
where column_default = '''0000-00-00 00:00:00''';


Since MySQL lacks the concept of schema, TABLE_SCHEMA is the same as the database

Note, that the default is '0000-00-00 00:00:00', so ' is part of the value. You, therefore, need to quote with an additional ' to get it right.

When you have fixed the default and whatever data there is, I suggest you set 'NO_ZERO_DATE' in sql_mode to prevent this in the future. 

'0000-00-00 00:00:00' have some counterintuitive properties, for example:

create table tt (x int, y datetime not null default '0000-00-00 00:00:00');
insert into tt (x) values (1);
select * from tt where y is null and y is not null;
+------+---------------------+
| x    | y                   |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+


You can change the default value like:

alter table tt alter column y set default '1900-01-01 00:00:00';


So you can use the previous query to generate alter statements like:

select 'ALTER ' || TABLE_SCHEMA || '.' || TABLE_NAME 
    || ' ALTER COLUMN ' || COLUMN_NAME || ' SET DEFAULT ''1900-01-01 00:00:00'';' 
from information_schema.columns 
where column_default = '''0000-00-00 00:00:00''';


Note that you need 'PIPES_AS_CONCAT' in sql_mode for || to work