List tables used by a view in MySQL database

Query below lists views in MySQL database with objects used by view.

Query was executed under 8.0.13 MySQL version.

Query

select vtu.view_schema as database_name,
       vtu.view_name as view_name,
       vtu.table_schema as referenced_database_name,
       vtu.table_name as referenced_object_name,
       tab.table_type as object_type
from information_schema.view_table_usage vtu
join information_schema.tables tab on vtu.table_schema = tab.table_schema
                                   and vtu.table_name = tab.table_name
where view_schema not in ('sys','information_schema',
                          'mysql', 'performance_schema')
    -- and tab.table_schema = 'database_name' -- put your database name here
order by vtu.view_schema,
         vtu.view_name;

Columns

  • database_name - view database (schema) name
  • view_name - view name
  • referenced_database_name - table database name that view is referring to
  • referenced_object_name - table name that view is referring to
  • object_type - type of referenced object:
    • BASE TABLE
    • VIEW

Rows

  • One row represents one view reference
  • Scope of rows: all views references in database
  • Ordered by view database (schema) name, view name

Sample results

0
There are no comments. Click here to write the first comment.