CODEWITHSUNDEEP
mysqlselectinformation-schema
Davide Zoccatelli
Davide Zoccatelli

Reputation: 47

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

Upvotes: 4

Views: 18932

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 4

Related Questions