Query below lists views in MySQL database with objects used by view.
Query was executed under 8.0.13 MySQL version.
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;
- 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
- One row represents one view reference
- Scope of rows: all views references in database
- Ordered by view database (schema) name, view name