Show table status in subquery?

Question

Is this supposed to work in MySQL?

select * from (show table status like '%fubar%') as t1; 

or even

 select name, rows from (show table status like '%fubar%') as t1 where rows>0; 

This is the error I get:

ERROR 1064 (42000): You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to the status of the show table, for example "% fubar%"), like t1 'on line 1

Can show table foo like '%something%' or show tables like '%something%' not be used in the subquery this way? How else could you choose from all tables matching a specific template?

Answer 1

 SELECT table_name as name, table_rows as rows FROM information_schema.tables as t1 WHERE table_rows > 0 

Below is an alternative way to get the information you are looking for.