请问mysql如何通过sql语句查询视图依赖哪些基表

查看表是否被哪些视图依赖

实例

create table test(id int, name varchar(10)); create view view_test as select * from test; alter table test alter name type varchar(100);

1
2
3

在这里插入图片描述

查看表被那些视图依赖

create or replace function recursive_get_deps_views(IN tbl oid, OUT oid oid, OUT relkind "char", OUT nspname name, OUT relname name, OUT deps oid[], OUT ori_oid oid, OUT ori_relkind "char", OUT ori_nspname name, OUT ori_relname name ) returns setof record as $$ declare begin return query with recursive a as ( select * from ( select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps,(select t1.oid from pg_class t1,pg_namespace t2 where t1.relnamespace=t2.oid and t1.oid=tbl) as ori_oid from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and t1.relkind in ('m','v') ) t where t.ori_oid = any(t.deps) union select * from ( select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps, a.oid as ori_oid from pg_class t1,pg_namespace t2,a where t1.relnamespace=t2.oid and t1.relkind in ('m','v') ) t where t.ori_oid = any(t.deps) ) select a.oid,a.relkind,a.nspname,a.relname,a.deps,a.ori_oid,b.relkind ori_relkind, c.nspname ori_nspname,b.relname ori_relname from a,pg_class b,pg_namespace c where a.ori_oid=b.oid and b.relnamespace=c.oid order by a.nspname,a.relkind,a.relname; end; $$ language plpgsql strict;