SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.
On server side, that is, from within MySQL itself, one cannot:
SELECT `Database` FROM (SHOW DATABASES);
One cannot:
DECLARE show_cursor CURSOR FOR SHOW TABLES;
One cannot:
SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';
So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading…
Bwahaha! A hack!
For some SHOW statements, there is a way around this. I’ve been banging my head against the wall for weeks now on this. Now I have a partial solution: I’m able to read SHOW output for several SHOW statements. Namely, those SHOW statements which allow a LIKE or a WHERE clause.
For example, most are familiar with the following syntax:
USE mysql; SHOW TABLE STATUS LIKE 'user';
However not so many know that any SHOW statement which accepts LIKE, can also accept WHERE: Continue reading » “Reading results of SHOW statements, on server side”