How to limit SHOW TABLES query

Question

I have the following query:

SHOW TABLES LIKE '$prefix%'

It works exactly how I want it to, though I need pagination of the results. I tried:

SHOW TABLES LIKE '$prefix%' ORDER BY Comment ASC LIMIT 0, 6

I need it to return all the tables with a certain prefix and order them by their comment. I want to have pagination via the LIMIT with 6 results per page.

I'm clearly doing something very wrong. How can this be accomplished?

EDIT: I did look at this. It didn't work for me.

Solution 1

The above cannot be done via MySQL Syntax directly. MySQL does not support the LIMIT clause on certain SHOW statements. This is one of them. MySQL Reference Doc.

The below will work if your MySQL user has access to the INFORMATION_SCHEMA database.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_TO SEARCH_HERE' AND TABLE_NAME LIKE "table_here%"  LIMIT 0,5;

Solution 2

Just select via a standard query instead of using SHOW TABLES. For example

select table_name from information_schema.tables

Then you can use things like ASC and LIMIT, etc...

Comments

[Jaxkr]

But how do I then filter if by prefix. The LIKE clause isn't working.

[Mike Mackintosh]

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'table%' LIMIT 0,5 works

[Jaxkr]

Thanks you. But that's returning results from databases that start with that prefix. Not tables that do.

[Mike Mackintosh]

I apologize. Try this: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_HERE' AND TABLE_NAME LIKE "table_here%" LIMIT 0,5;