Dependent nested sp's

Karthik G

10y996

orite

Is it possible to write a PL/SQL query/procedure to identify a complete list of a stored procedures dependencies? I'm only interested in identifying other stored procedures and I'd prefer not to limit the depth of nesting that it gets too either. For example, if A calls B, which calls C, which calls D, I'd want B, C and D reported as dependencies for A.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthik G10y ago
no,thr is nothing like that.
Atul Kumar10y ago
I guess you would need to have some sort of DB permissions to view it's dependencies.
Please check on this link for more info

http://dba.stackexchange.com/questions/29385/what-permissions-are-needed-to-view-dependencies

it is mentioned there that
"Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set)"
Karthik G10y ago
SELECT*
FROM sys.dm_sql_referencing_entities ('ECI_Hr_ECI_Sp_SbtHSav', 'OBJECT')
....i tried like this.
Karthik G10y ago
columns which ever you mentioned in given query...those all are not available in my sys.dm_sql_referencing_entities.
i iam selecting all from those are reflecting as blank.
Atul Kumar10y ago
can you mention the type of error or what errors you are getting??
Karthik G10y ago
thank you,
i tried this.
but it's not working.getting some errors.
i hope there is another method to solve this.

Atul Kumar10y ago

You can try this.

This query displays all referenced objects(objects store procedure depends upon) in stored procedures.

```
USE MyDatabase; 
```

GO SELECT referenced_schema_name, referenced_entity_name,

referenced_minor_name,referenced_minor_id, referenced_class_desc,

```
is_caller_dependent,
```
```
is_ambiguous 
```

FROM sys.dm_sql_referencing_entities ('MyStoreProcedure', 'OBJECT');

```
GO
```

Please mark accepted if found helpful