Select data from INFORMATION_SCHEMA query

mysql select information-schema
Davide Zoccatelli picture Davide Zoccatelli · Jun 26, 2013 · Viewed 10.5k times · Source

Probably an easy question.. I've got a list of tables from INFORMATION_SCHEMA and I want to do queries (select, delete etc) on the data within these tables:

I tried

Select * from (SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'Col1')

But of course it does not work..

Answer

M Khalid Junaid picture M Khalid Junaid · Jun 26, 2013

You have to provide the alias for the table clause after FROM and in SELECT like q.*

SELECT q.* FROM (SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Col1') q

All you can see from INFORMATION_SCHEMA is 

SELECT q.* FROM (SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id') q

But for data you have to reference the database with table name separately

Related questions

Can DESCRIBE syntax be embedded in SELECT statement?

In MySQL, the syntax DESCRIBE can show a table's structure, but it cannot be embedded to normal statement; is there some tricky way to do it? For example, this shows the table1 structure, returned as a table (but SQL statement …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More