CODEWITHSUNDEEP
mysqlsql
carpics
carpics

Reputation: 2282

Sql loop through Database tables

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:

  1. TableName
  2. MaxID - of the table
  3. 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

Upvotes: 4

Views: 1252

Answers (2)

cmerriman
cmerriman

Reputation: 315

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.

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

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.

Upvotes: 0

Related Questions