How to list tables used in a view with mysql

Question

I need a list of tables being used in a view in mysql. For example, if I have a view like:

SELECT * FROM table1
JOIN  table2
ON table1.id = table2.id

I want to get: table1,table2

Answer 1

Unfortunately, I don't believe that's possible directly. Instead, you need to query and parse the actual view definition:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE
TABLE_NAME = ?;

Answer 2

.

mysql> CREATE VIEW vw_test AS  
    -> SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
    -> WHERE TABLE_NAME = 'vw_test';
+------------------------------------------------------------------+
| VIEW_DEFINITION                                                  |
+------------------------------------------------------------------+
| select * from table1 join table2 on table1.id = table2.id;       |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

then you could use any of the following tools to parse the table names:

1. Terence Parr's ANTLR parser generator (Java, but can generate parsers in any one of a number of target languages) has several SQL grammars available, including a couple for PL/SQL, one for a SQL Server SELECT statement, one for mySQL, and one for ISO SQL - (http://www.antlr.org/grammar/list).

   I took this from SO answer here: SQL parser library for Java - Retrieve the list of table names present in a SQL statement

2. Data Tools Project - SQL Development Tools (http://www.eclipse.org/datatools/project_sqldevtools/).

   Here's the documentation for the SQL Query Parser (http://www.eclipse.org/datatools/project_sqldevtools/sqltools_doc/SQL%20Query%20Parser%20User%20documentation.htm).

3. Here's a blog with descriptions of how to "Get columns and tables in SQL script (Java version)" http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/get-columns-and-tables-in-sql-script/

4. Or write yourself a custom mySQL proc based on the following (found here - http://www.sqlparser.com/fetch-table-column-name-example-extact-all-table-field-name.php):

   SELECT c_mandant, hist_datum, parkey1, parkey2, funktionscode, ma_parkey, me_parkey 
    , CASE WHEN EXISTS (SELECT 1 
                          FROM CDS_H_GRUPPE  GRP1 
                         WHERE GRP1.c_mandant = c_mandant 
                           AND GRP1.hist_datum    = ADD_MONTHS(LAST_DAY(TRUNC(SYSDATE)), -1) 
                           AND GRP1.funktionscode = 'H' 
                           AND GRP1.parkey1       = ma_parkey) 
             THEN 1 
         ELSE NULL 
      END MA_ME 
    , CASE WHEN EXISTS (SELECT 1 
                          FROM CDS_H_GRUPPE    GRP2 
                         WHERE GRP2.c_mandant     = c_mandant 
                           AND GRP2.hist_datum    = ADD_MONTHS(LAST_DAY(TRUNC(SYSDATE)), -1) 
                           AND GRP2.funktionscode = 'U' 
                           AND GRP2.parkey1       = me_parkey) 
             THEN 1 
         ELSE NULL 
      END ME_MA 
    , ROW_NUMBER() OVER (PARTITION BY c_mandant, ma_parkey, me_parkey ORDER BY c_mandant, ma_parkey, me_parkey)  ANZ_MA 
     FROM (SELECT c_mandant, hist_datum, parkey1, parkey2, funktionscode 
            , CASE WHEN funktionscode = 'U' 
                     THEN parkey1 
                 ELSE parkey2 
              END MA_PARKEY 
            , CASE WHEN funktionscode = 'U' 
                     THEN NULL 
                 ELSE parkey1 
              END ME_PARKEY 
         FROM 
              CDS_H_GRUPPE 
        WHERE 
              funktionscode IN ('U', 'H') 
          AND hist_datum    = ADD_MONTHS(LAST_DAY(TRUNC(SYSDATE)), -1) 
      ) 
   

Answer 3

this is what you want... this can get table used in Views and table which joined together.. but it can get one join... if want more add few more hint..

hope this would solve your question...

select 
    case 
        when view_definition regexp '.*from +.*'
        then substring_index(substring_index(view_definition, 'from ', -1), ' ', 1)
    end as 'primary table',
    case 
        when view_definition regexp '.*join +.*'
        then substring_index(substring_index(view_definition, 'join ', -1), ' ', 1)
    end as 'joined table'
from information_schema.views where table_name="YOUR VIEW NAME" and table_schema="shotbot_production";

Answer 4

No that is not possible. You have to look for the definition of the view and get that done by yourself manually.