How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL
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