Show table status in a subquery?

Question

Is this not 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'm receiving:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show table status like '%fubar%') as t1' at line 1

Can show table foo like '%something%' or show tables like '%something%' not be used in a subquery in this way? How else could you select from all tables matching a certain pattern?

Answer 1

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

Here is an alternate way of retrieving the information you are looking for.