How to find all invalid views in mysql?

Question

So, we managed to do interesting things to our database that created invalid views. We just want to drop these views from the database and move on. What I could not find is an easy way to find all invalid views in the database so that I can work from there. Is there an easy way to do this?

Recipe to create an invalid view

create table some_table (some_column varchar(20));
insert into some_table(some_column) values('some_data');
create view some_view as (select some_column from some_table);
select * from some_view;

# Now drop the table and test the view
drop table some_table;
select * from some_view;

Answer 1

The solution from Ralph works fine. If you want a query without subselect, try this:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'view'
  AND table_rows is null
  AND table_comment like '%invalid%'

The condition table_rows is null is important as it will force an evaluation of the view and the error message in the table_comment column.

If afterwards you want to fix your view, you can see the original definition with

SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'your_database'
  AND table_name = 'your_view'

Answer 2

A better way of finding broken views within your MySQL database:

SELECT vws.table_schema,vws.table_name 
FROM (
    SELECT * 
    FROM  information_schema.tables 
    WHERE table_type='VIEW' 
        AND table_comment LIKE '%invalid%'
  ) vws;

Original source of query