Sql loop through database tables

Question

In my database I have 20 tables.

One of the tables called "Connections" contains two columns, "Table Name" and "NextHi"

+--------+-------------+ | NextHi | TableName | +--------+-------------+ | 43 | Page | +--------+-------------+ | 32 | User | +--------+-------------+ 

So, in the column "TableName" there are names of other tables.

Every other table has an "Id" column.

I need a Script that will cycle through all the tables in the database, and the fore ach table returns a single row with 3 columns:

• Tablename
• MaxID - tables
• NextHi value from the Connections table for the current table

Thus, the result should look like this:

 +-----------+-------------+-------+ | TableName | NextHi | MaxId | +-----------+-------------+-------+ | Page | 43 | 435 | +-----------+-------------+-------+ | User | 32 | 768 | +-----------+-------------+-------+ 

I have a Script that returns this to me, but it has a choice for each table, and the table names are hardcoded:

 ( SELECT "User" as `TableName`, `Connections`.`NextHi`, (SELECT MAX(`Id`) FROM `User`) as `MaxId` FROM `Connections` WHERE `Connections`.`TableName` = "User" ) UNION ALL ( SELECT "Page" as `TableName`, `Connections`.`NextHi`, (SELECT MAX(`Id`) FROM `Page`) as `MaxId` FROM `Connections` WHERE `Connections`.`TableName` = "Page" ) 

But I need this in a loop, as one Choose

EDIT:

Bugfix: you don’t really just have to select one, but it should be easier than mine now

Answer 1

 select NextHi, TableName, auto_increment from information_schema.tables, Connections where table_schema='databaseName' and table_name=TableName; 

Edit: after posting this post, I see that @BryanT already commented on this during testing.

Answer 2

You need a function called "dynamic SQL" that is not supported in MySQL, but it can be executed .

You need to write a query that gives the correct SQL statement as a result of the Connections query, and then execute that statement.