I want to select the nth element of the table list provided by the SHOW TABLES query. If I use the following code to list them alphabetically:

WITH tableslist as (SHOW TABLES)
SELECT Tables_in_DBname FROM tableslist ORDER BY Tables_in_DBname

I get an error regarding the syntax near 'SHOW TABLES). If I only do the SHOW TABLES query I get a table with the column Tables_in_DBname.

The main goal of this would be to populate a checkbox in VBA with the table names in the database, so in case I am looking in the completely wrong direction to go about this please correct me. I am able to populate the cbo with the fields of specific tables, but I could not find a way to list the tables from the database as fields, so I am attempting to make an ordered list to select the nth element from.

The server type is MariaDB.

CodePudding user response：

You can use information_schema.tables to get the list of tables:

SELECT table_name FROM information_schema.tables
  WHERE table_schema = '<name of database>' ORDER BY table_name;

This allows you to build more complex queries that inspect the tables in the database.