1

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.
Terminater
  • 186
  • 7
  • Why it is tagged sql-server? Are you looking same for sql server? – Kannan Kandasamy Sep 16 '16 at 08:34
  • Sorry, this is the first time I ever posted a question in stackoverflow and that sql-server tag came of as a suggestion. So I selected it unintentionally. I'm looking the answer for MySQL4.1 (to be precise). – Terminater Sep 16 '16 at 08:37

1 Answers1

1

You can use information schema

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
 WHERE table_schema = 'db_name'
 [AND table_name LIKE 'urtable']
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • Thanks for the reply, but I have already mentioned I am using MySQL 4.1 and the information_schema database is not yet introduced in that version of MySQL server. So, I'm expecting a work around the inofrmation_schema. Thanks again. – Terminater Sep 16 '16 at 11:07