Naveh Naveh - 6 months ago 14
SQL Question

Getting table name by position(row) from database

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.

Answer

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