Use 'show query' as a subquery to count the rows in MySQL 4.1

Question

I have MySQL 4.1 server version installed. I want to use the 'show query' as a subquery in a SELECT statement. For example:

SELECT count(*) from (SHOW VARIABLES LIKE 'log_bin');

But this gives me an error like:

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 VARIABLES LIKE 'log_bin')' at line 1

As per my understanding and my research on web, I figured that 'show queries' can't be used as subqueries.

Well I came across the function called FOUND_ROWS(). But this function returns me 1 on any 'show query' I do. For example:

show tables; select FOUND_ROWS();

And gives me this:

+----------------------+
| Tables_in_test       |
+----------------------+
| test1                |
| test2                |
+----------------------+
2 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

As I use MySQL 4.1, the information_schema database is not yet introduced. Is there any other way I could solve my issue?

Conditions:

• I have to use MySQL 4.1
• Should get count using MySQL queries.

Answer 1

You can use information schema

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
 WHERE table_schema = 'db_name'
 [AND table_name LIKE 'urtable']