(8.0.31)
https://www.dbrnd.com/2015/08/find-table-dependency-in-constraint-view-stored-procedure-of-mysql/
調べた限りない模様
drop table tab1;
create table tab1(col1 int);
drop view view1;
create view view1 as select * from tab1;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
select count(*) from view1;
end
//
delimiter ;
select table_schema,table_name
from information_schema.views
where table_schema='test'
and view_definition like '%tab1%'
;
select routine_schema,routine_name,routine_type
from information_schema.routines
where routine_schema ='test'
and routine_definition like '%view1%'
;
all_dependencies
drop table tab1 purge;
create table tab1(col1 int);
drop view view1;
create view view1 as select * from tab1;
drop procedure proc1;
set serveroutput on;
create or replace procedure proc1
as
i int;
begin
select count(*) into i from view1;
dbms_output.put_line(i);
end;
/
select * from all_dependencies
where owner = 'TEST'
and name in ('TAB1','VIEW1','PROC1')
;
TAB1 -> VIEW1 -> PROC1の依存関係を確認可能
(15)
https://www.postgresql.jp/document/14/html/catalog-pg-depend.html
pg_depend
pg_rewrite
drop table tab1;
create table tab1(col1 int);
drop view view1;
create view view1 as select * from tab1;
drop procedure proc1;
create or replace procedure proc1()
language plpgsql
as $$
declare
i int;
begin
select count(*) strict into i from view1;
raise notice '%',i;
end;
$$;
select oid,relname,relkind from pg_class
where relname = 'tab1'
and relnamespace = 'public'::regnamespace
;
oid | relname | relkind
-------+---------+---------
90165 | tab1 | r
select objid,objsubid, refobjid,refobjsubid from pg_depend
where refobjid = 90165
;
objid | objsubid | refobjid | refobjsubid
-------+----------+----------+-------------
90167 | 0 | 90165 | 0
90171 | 0 | 90165 | 1 ★こちらを使用する
select ev_class from pg_rewrite where oid = 90171;
ev_class
----------
90168
select oid,relname,relkind from pg_class
where oid = 90168
;
TAB1 -> VIEW1 の依存関係を確認可能
oid | relname | relkind
-------+---------+---------
90168 | view1 | v
--
select oid,proname from pg_proc
where proname = 'proc1'
and pronamespace = 'public'::regnamespace
;
oid | proname
-------+---------
90172 | proc1
select objid,objsubid, refobjid,refobjsubid from pg_depend
where refobjid = 90172
;
→データなし
VIEW1 -> PROC1の依存関係は確認できない模様
tab1の削除時は下記メッセージが表示される
ERROR: cannot drop table tab1 because other objects depend on it
DETAIL: view view1 depends on table tab1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
view1は削除できるので、ビューとプロシージャの依存関係は管理されていないと思われる
(テーブルとプロシージャの依存関係も同様)
select oid,proname from pg_proc
where prosrc like '%view1%'
;
(2019)
https://miho-matsumoto.hatenadiary.org/entry/20100520/1274313090
https://learn.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-ver16
sys.sql_expression_dependencies
drop table tab1;
create table tab1(col1 int);
drop view view1;
create view view1 as select * from tab1;
drop procedure proc1;
create or alter procedure proc1
as
begin
declare @i int;
set @i = (select count(*) from view1);
print @i
end
go
select object_name( referencing_id ) entity_name, referenced_entity_name
from sys.sql_expression_dependencies
where object_name( referencing_id ) in ('tab1','view1','proc1')
;
TAB1 -> VIEW1 -> PROC1の依存関係を確認可能
