Vinay Venu Vinay Venu - 1 year ago 151
MySQL Question

How to find all invalid views in mysql?

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 Source

Based on an answer that was somehow deleted.

SELECT CONCAT('CHECK TABLE ', table_name, ';') AS my_view_check_statements
FROM information_schema.views 
WHERE table_schema = 'your_database_name'
INTO OUTFILE '/tmp/chkstmts.sql';

source '/tmp/chkstmts.sql';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download