Search code examples
mysqlselectinformation-schema

Select data from INFORMATION_SCHEMA query

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..

Solution

  • 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