作者fsz570 (570)
看板Database
标题Re: [SQL ] 请问一个Oracle语法
时间Tue Mar 31 20:41:47 2009
※ 引述《ling123 (@@)》之铭言：
: 请问一个Oracle语法
: 要如何知道该Function/Procedure/View
: References到哪个Table或Function阿?
: 在PL/SQL Developer这套工具可以看到References资讯
: 那要如何用SQL语法串出呢?
: 谢谢~
基本上是用 SYS.DBA_OBJECTS 和 PUBLIC_DEPENDENCY 这两个串出来的
如底下 SQL, 如果你权限不足, 试着用 USER_OBJECTS 看看
Select a.object_type, a.object_name, b.owner AS related_owner,
b.object_type AS related_object_type,
b.object_name AS related_object_name, b.status
from sys.dba_objects a,
sys.dba_objects b,
(Select object_id, referenced_object_id
FROM public_dependency
start with object_id =
(Select object_id
from sys.dba_objects
WHERE owner = '' -- your schema name here
AND object_name = '' -- your procedure name here
AND object_type = 'PROCEDURE')
connect by prior referenced_object_id = object_id) c
where a.object_id = c.object_id
and b.object_id = c.referenced_object_id
and a.owner not in ('SYS', 'SYSTEM')
and b.owner not in ('SYS', 'SYSTEM')
and a.object_name <> 'DUAL'
and b.object_name <> 'DUAL';
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 118.160.109.245
1F：推 ling123:谢谢阿~ 04/03 13:00
2F：推 ling123:Start With这种语法第一次看过 好神阿 04/03 14:09