oracle查询存储过程依赖的表,查看存储过程依赖的表

DECLARE @procname NVARCHAR(100) = 'dbo.test_tbl'

SELECT DISTINCT @procname procname,SCHEMA_NAME(t.[schema_id]) + '.' + OBJECT_NAME(referenced_major_id)

AS tblname,referenced_major_id

FROM sys.sql_dependencies

AS sd

INNER JOIN sys.tables AS t ON t.[object_id] = sd.referenced_major_id AND

t.[type] = N'U'

WHERE sd.[object_id] = OBJECT_ID(@procname)

DECLARE @procname NVARCHAR(100) = 'dbo.test_tbl'

SELECT DISTINCT o.name procname,SCHEMA_NAME(t.[schema_id]) + '.' + OBJECT_NAME(referenced_major_id)

AS tblname,referenced_major_id

FROM sys.sql_dependencies

AS sd

INNER JOIN sys.tables AS t ON t.[object_id] = sd.referenced_major_id AND

t.[type] = N'U'

INNER JOIN sys.all_objects o ON(o.[type] = 'P' OR o.[type] = 'X' OR o.[type] = 'PC')

AND o.[object_id] = sd.[object_id]

SELECT * FROM sys.all_objects

WHERE([type] = 'P' OR [type] = 'X' OR [type] = 'PC')

ORDER BY [name]

update statistics tblname with fullscan