Sql loop through Database tables

Question

In my database I have 20 tables.

One of the table is named "Connections" contains two columns "TableName" and "NextHi"

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

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

Each other Table has Column "Id"

What I need is Script which will loop trough all tables in database and fore ach table return one row with 3 Column:

TableName
MaxID - of the table
NextHi value from "Connections" table for current Table

So result should be like this:

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

I have script which returns me this, but it has Select for each Table, and table names are hard coded:

(
  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 loop as one Select

EDIT:

Correction: It doesn't really need to be just one Select, but it should be simpler then what I have now

So you table Connections contains a list of all your DB tables? — Haytem BrB, Feb 02 '16 at 14:14
Yes it contains a list of all my DB tables. Also in that list there are one or two additional table names which doesn't exit int my DB. But if that is big deal we can ignore it, I can remove them. — carpics, Feb 02 '16 at 14:17
I don't think this is doable in one Select as you need to use prepared statements: http://stackoverflow.com/questions/19510490/sql-query-dynamic-table-name-in-for — Haytem BrB, Feb 02 '16 at 14:21
Would it help to be able to read mysql's own metadata? e.g. SELECT table_name FROM information_schema.tables WHERE table_schema= 'yourdb' — BryanT, Feb 02 '16 at 14:22

score 2 · Answer 1 · edited Feb 02 '16 at 15:09

2

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

Edit: After posting this I see that @BryanT had already commented with this while I was testing.

edited Feb 02 '16 at 15:09

Gouda Elalfy

6,888
1
26
38

answered Feb 02 '16 at 14:29

cmerriman

305
1
7

*auto_increment* would of course only work if `ID` column of all the mentioned tables have auto_increment enabled. – Sameer Mirji Feb 02 '16 at 14:38
auto_inrement is not enabled for ID of these tables. I need Max(ID) – carpics Feb 02 '16 at 15:17

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

You need a feature called "dynamic SQL", which isn't supported well in MySQL, but it can be done.

You need to write a query which yields a valid SQL statement as a result by querying Connections, and then execute that statement.

edited May 23 '17 at 11:59

Community

1
1

answered Feb 02 '16 at 14:37

Neville Kuyt

29,247
1
37
52

Sql loop through Database tables

2 Answers2