Finding the tables where a view is derived from in MySQL

Question

I have a view in MySQL called myview that has 3 fields: col1, col1, col3.

I would like to figure out where these columns came from, e.g., which table does col1 live in? And col2? etc.

I can find out where col1 comes from by using

SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'col1'
        AND TABLE_SCHEMA='lava_mac';  

From this I would get 2 matches,

myview  
tableWithCol1 

This is great, but I really want to extend this query in such a way that I get a result like:

col1         col2          col3  
tableWithCo1 tableWithCol2 tableWithCol3

This question is similar, but not the same. Does anyone have any advice? Thanks.

Answer 1

Edit: Mysql version How to find all the tables in MySQL with specific column names in them?

SQL Server version: http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

Hope it helps

Answer 2

Approach 1 (Group_Concat)

If you know the name of the view you could do something such as:

select
GROUP_CONCAT(COLUMN_NAME) as columns,
GROUP_CONCAT(TABLE_NAME) as tables 
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = "myView";

Data:

columns         tables
col1,col2,col3  myview,myview,myview

Approach 2 (Simple)

  select
    COLUMN_NAME,
    TABLE_NAME 
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = "myView";

Approach 3 (CASE with ALIAS)

Unfortunately mysql does not have transpose, so you must hardcode the column names like:

SELECT GROUP_CONCAT(CASE WHEN COLUMN_NAME = 'col1' THEN TABLE_NAME END) as `col1`,
       GROUP_CONCAT(CASE WHEN COLUMN_NAME = 'col2' THEN TABLE_NAME END) as `col2`,
       GROUP_CONCAT(CASE WHEN COLUMN_NAME = 'col3' THEN TABLE_NAME END) as `col3`
  FROM
INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like "myView%";

SQLFIddle: http://sqlfiddle.com/#!9/ceb96e/10

Answer 3

I'm thinking a

                     SHOW CREATE VIEW myview

statement would return what you're looking for.

(Is there a need to re-invent the wheel, or maybe I'm not understanding the question.)