How to obtain MySQL views dependency (the table you are using)

There are times when you want to find out which table is being used in a view. However, MySQL does not have a dictionary view to check dependencies, such as Oracle’s ALL_DEPENDENCIES table. VIEWS view, which contains the VIEW DDL statements, and the INFORMATION_SCHEMA.TABLES view, which contains the definition information for TABLEs (including VIEWs). VIEWS view and the INFORMATION_SCHEMA.

Script to check VIEW dependencies (one level only)

The SQL script to check the dependencies looks like this.

SELECT UPPER(V.TABLE_SCHEMA) SCHEMA_NAME
     , UPPER(V.TABLE_NAME) VIEW_NAME
     , UPPER(T.TABLE_NAME) TABLE_NAME
     , T.TABLE_TYPE TYPE
  FROM INFORMATION_SCHEMA.TABLES T 
 INNER JOIN INFORMATION_SCHEMA.VIEWS V 
    ON V.TABLE_SCHEMA = T.TABLE_SCHEMA
   AND V.VIEW_DEFINITION LIKE CONCAT('%`',T.TABLE_NAME,'`%')
 WHERE T.TABLE_SCHEMA = DATABASE()
   AND V.TABLE_NAME = UPPER('V_D')
;

Specify the name of the VIEW to be examined in V.TABLE_NAME on line 10. In the example, we are examining a VIEW named “V_D”.

Note that the DEFAULT schema must be specified at the time of connection; if the DEFAULT schema is not specified, “DATABASE()” in line 9 should be the name of the schema (DB name) to be investigated.

The execution result can be obtained like this.

You can see that the view “V_D” that we investigated consists of the T_H table, the T_M table, and the V_U view.
The “VIEW_NAME” column outputs the name of the VIEW under investigation. The “TABLE_NAME” column will output the name of the TABLE or VIEW that has a dependency. The “TYPE” column will be “TABLE_NAME”.

The SQL script introduced above cannot investigate the dependencies of VIEWs used in VIEWs. (In the example above, it doesn’t even check the table used by the V_U view.

Script for examining VIEW dependencies (improved version: multi-level investigation)

To investigate the dependency of VIEWs used in VIEWs, use recursive queries to investigate the dependency of VIEWs again for information whose “TYPE” column is VIEW. Since a recursive query is used, it will take some time if there are a large number of TABLEs and VIEWs created. The SQL script for the recursive query looks like this

WITH RECURSIVE VIEW_REC AS (
SELECT UPPER(V.TABLE_SCHEMA) SCHEMA_NAME
     , 1 HIERARCHY
     , UPPER(V.TABLE_NAME) VIEW_NAME
     , UPPER(T.TABLE_NAME) TABLE_NAME
     , T.TABLE_TYPE TYPE
  FROM INFORMATION_SCHEMA.TABLES T 
 INNER JOIN INFORMATION_SCHEMA.VIEWS V 
    ON V.TABLE_SCHEMA = T.TABLE_SCHEMA
   AND V.VIEW_DEFINITION LIKE CONCAT('%`',T.TABLE_NAME,'`%')
 WHERE T.TABLE_SCHEMA = DATABASE()
   AND V.TABLE_NAME = UPPER('V_D')
UNION ALL
SELECT UPPER(V.TABLE_SCHEMA) SCHEMA_NAME
     , HIERARCHY + 1 AS HIERARCHY
     , UPPER(V.TABLE_NAME) VIEW_NAME
     , UPPER(T.TABLE_NAME) TABLE_NAME
     , T.TABLE_TYPE TYPE
  FROM INFORMATION_SCHEMA.TABLES T 
 INNER JOIN INFORMATION_SCHEMA.VIEWS V 
    ON V.TABLE_SCHEMA = T.TABLE_SCHEMA
   AND V.VIEW_DEFINITION LIKE CONCAT('%`',T.TABLE_NAME,'`%')
 INNER JOIN VIEW_REC R
    ON V.TABLE_SCHEMA = R.SCHEMA_NAME
   AND V.TABLE_NAME = R.TABLE_NAME
)
SELECT * FROM VIEW_REC
ORDER BY HIERARCHY
       , VIEW_NAME
;

As before, specify the VIEW name to be investigated in V.TABLE_NAME in line 12. If the DEFAULT schema is not specified, specify the name of the schema (DB name) to be investigated for “DATABASE()” in line 11.

The execution result can be obtained like this.

You can see that the M_USER table used by the V_U view has been investigated. The “HIERARCHY” column outputs the depth of the hierarchy.
A row with HIERARCHY of 1 is a TABLE or VIEW that is used directly from the target VIEW.