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
– Haytem BrB, Commented Feb 2, 2016 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
– carpics, Commented Feb 2, 2016 at 14:17
I don't think this is doable in one Select as you need to use prepared statements: stackoverflow.com/questions/19510490/… — Haytem BrB
– Haytem BrB, Commented Feb 2, 2016 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
– BryanT, Commented Feb 2, 2016 at 14:22

Gouda Elalfy · Accepted Answer · 2016-02-02 15:09:34Z

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 2, 2016 at 15:09

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

answered Feb 2, 2016 at 14:29

cmerriman

3151 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sameer Mirji Over a year ago

auto_increment would of course only work if ID column of all the mentioned tables have auto_increment enabled.

carpics Over a year ago

auto_inrement is not enabled for ID of these tables. I need Max(ID)

Community · Accepted Answer · 2017-05-23 11:59:15Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered Feb 2, 2016 at 14:37

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Collectives™ on Stack Overflow

Sql loop through Database tables

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related