Mysql – In MySQL, Is there a way to display all tables contained in a view

database-designMySQLview

I have many views in my database and I wanted to do some statistics about table usage (not dynamic statistics but static ones).

Like for this view:

CREATE VIEW V_LOWER_COST AS
    SELECT SUPPLIER_NUMBER, A.ITEM_NUMBER,UNIT_COST, SUPPLIER_COST
        FROM INVENTORY_LIST A, SUPPLIERS B
        WHERE A.ITEM_NUMBER = B.ITEM_NUMBER
        AND UNIT_COST > SUPPLIER_COST

… I would get something like this:

view-name       | tables 
V_LOWER_COST    | INVENTORY_LIST, SUPPLIERS

and for the view:

CREATE VIEW SALESORDERTOP AS
    SELECT X.SALESORDERNUMBER, X.TOTALDUE, X.COMPANYNAME, C.UNIT_COST 
    FROM V_LOWER_COST C
       INNER JOIN TABLEX X ON C.KEY = X.KEY 
       LEFT JOIN TABLEY ON X.KEY = Y.KEY 
    WHERE TOTALDUE > 10000.0
    AND Y.REGION = 'AU'

… I would get something like this:

view-name      | tables     
SALESORDERTOP  | INVENTORY_LIST, SUPPLIERS, TABLEX, TABLEY

Note that the second view refers to the first one and the report will mention all implied table including the ones from the first view.

I tried many queries using the information_schema but was blocked at some point as not able to make a like which refers to other records.

Is there any existing command to find that easily otherwise what would be the best solution to do so?

Best Answer

Update: You can use sql parsers for this here is a demo page for the one I tested http://107.170.101.241:8080/getTableColumn/

Here are other useful links: [1] [2]


I personally found this interesting so here is what I have tried so far:

Basic idea, table names are being preceded by Keywords like Left join , From ,etc (thus same logic can be repeated for other types of joins as well) so what I am trying to do here is locate the keyword and find there position using locate function then using that position (which I got as output) in a substring command after adding some extra sum to the location which is equal to no of digits in keyword eg: 4 for FROM which is in turn is skipping the keyword itself from occurring in results and resulting in the table name followed by some extra followup characters,which i think can be replaced.

Thoughts:The catch here will be to know the length of each table name and then passing it as the variable to the last parameter of the substring function and then iterating the below query for each view may be using a batch script which will yield only the table name in turn as a result.

Note : This is just an work in progress and still needs further improvements.

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15) as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15)as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS 
WHERE locate('left join',VIEW_DEFINITION)>0;

Another updated format:

    SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15), ',', "\n|"),
 ".", "\n"), "(", "") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15), ',', "\n|"),
 ".", "\n"), "(", "")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS 
WHERE locate('left join',VIEW_DEFINITION)>0 ;

To also show where it came from for Eg: join,from etc

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS 
WHERE locate('left join',VIEW_DEFINITION)>0 ;

Please check it here to see what I mean for now the main purpose is finding the table names used by the view,grouping can be done as we move forward using a temporary table may be http://rextester.com/NXFM21955

Please use this link for live cooperation,testing and further improvement http://rextester.com/live/UFEV3010