如何查询构成materialized view（物化视图）的所有关联基础表

以下是不同主流数据库的实现方案，查询结果均匹配你需要的view name | table name输出格式：

Oracle 数据库（适配你示例中的语法场景） #

直接查询系统依赖视图ALL_DEPENDENCIES即可，如果你有DBA权限可替换为DBA_DEPENDENCIES查询全库范围的依赖：

-- 直接查询直接依赖的表/视图
SELECT 
  name AS "view name",
  referenced_name AS "table name"
FROM 
  all_dependencies
WHERE 
  type = 'MATERIALIZED VIEW'
  AND name = 'TABLE_VM' -- 替换为你的物化视图名称，Oracle默认大写存储
  AND referenced_type = 'TABLE';

如果物化视图嵌套了其他视图，需要递归查询到最底层的关联表，可使用以下语句：

-- 递归查询所有底层关联表（包含嵌套视图依赖的表）
SELECT 
  CONNECT_BY_ROOT name AS "view name",
  referenced_name AS "table name"
FROM 
  all_dependencies
WHERE 
  referenced_type = 'TABLE'
  START WITH type = 'MATERIALIZED VIEW' AND name = 'TABLE_VM'
  CONNECT BY PRIOR referenced_name = name AND PRIOR referenced_owner = owner;

PostgreSQL 数据库 #

查询系统表依赖获取结果：

-- 直接查询直接依赖的表
SELECT 
  mv.relname AS "view name",
  dep_table.relname AS "table name"
FROM 
  pg_class mv
JOIN 
  pg_depend d ON d.objid = mv.oid
JOIN 
  pg_class dep_table ON d.refobjid = dep_table.oid
WHERE 
  mv.relkind = 'm' -- 筛选物化视图
  AND mv.relname = 'table_vm' -- 替换为你的物化视图名称
  AND dep_table.relkind = 'r' -- 筛选普通表
  AND d.deptype = 'n';

MySQL 8.0+ 数据库 #

SELECT 
  TABLE_NAME AS "view name",
  REFERENCED_TABLE_NAME AS "table name"
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
  TABLE_NAME = 'table_vm' -- 替换为你的物化视图名称
  AND REFERENCED_TABLE_NAME IS NOT NULL;

内容的提问来源于stack exchange，提问作者Giancarlo