Getting table name by position(row) from database

Question

How can I get a table name by his position(row)? I got many tables.

For example in columns to find from a table it works this way:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'database name here' 
AND TABLE_NAME = 'table name here' 
AND ORDINAL_POSITION = 2;

I need something like this only to find table name by their position(row) in the database.

Using MySQL. Thanks.

Can you clarify what exactly you're trying to achieve? Perhaps an example? It's a bit hard to understand the question. — Mureinik, May 21 '16 at 10:45
For example getting the second/fifth table name in a database. — , May 21 '16 at 11:16
Why don't you just use `LIMIT 1 OFFSET 1` to get the 2nd row? — Paul Spiegel, May 21 '16 at 13:28

angel.bonev · Accepted Answer · 2016-05-21T13:19:56.830

0

If I understand you correctly, you need something like that

SELECT position, TABLE_NAME
FROM (

  SELECT @row := @row +1 AS position, TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  JOIN (
   SELECT @row :=0
  )r
  WHERE TABLE_SCHEMA =  'TABLE_SCHEMA here'
)tmp
WHERE position =5

and a different approach

SET @row =0;
SELECT TABLE_NAME
FROM (
   SELECT @row := @row +1 AS position, TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA =  'TABLE_SCHEMA here'
)tmp
WHERE position =5

edited May 21 '16 at 13:19

answered May 21 '16 at 11:04

angel.bonev

2,154
3
20
30

It works, but I only need the name of the table, I don't need position and name to be display (When I said position I meant using it in the WHERE). – May 21 '16 at 12:28
you can select what ever you want SELECT TABLE_NAME FROM (...) WHERE position IN (1,5,6) – angel.bonev May 21 '16 at 12:33
It works in MySQL, but when I try to print it in my website it say: Parameter '@row' must be defined.] – May 21 '16 at 12:55
`DECLARE @row int;SET @row = 0;` if this doesn't work read about your problem [here](http://stackoverflow.com/questions/5524632/how-can-i-use-a-mysql-user-defined-variable-in-a-net-mysqlcommand) – angel.bonev May 21 '16 at 13:31
@Naveh I am pleased you got an answer you are happy with! – angel.bonev May 21 '16 at 14:07

score 0 · Answer 2 · answered May 21 '16 at 13:40

Looks like you want something like that:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'database name here' 
AND TABLE_NAME = (
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'database name here'
    ORDER BY CREATE_TIME ASC
    LIMIT 1  -- take one
    OFFSET 1 -- after 1st row
)

This will return all colums from the table that was created as second table for that DB.

Getting table name by position(row) from database

2 Answers2