How to check the dependencies in HANA

[Former Member]

Hi All,

Like the "Where-Used" functionality for AT/AN/CV in HANA, is there a view or a table that can be queried to get the dependency on a table/simple view?

To make things more clear, I am looking for a way to find out where-all an object (table/simple view/AT/AN/CV) is being referenced. For e.g., a table can be queried in a Stored Proc or one of the Content Views or even a Simple View. Similarly, one of the Content Views can be used in a Stored Prod or a Simple View. Is there a way to query this in HANA?

Regards,

Gaurav

[Former Member · Accepted solution]

I'd encourage you to look at the "SAP HANA SQL and System Views Reference" found at help.sap.com/hana_appliance - you'll find lots of useful system tables and views!

That being said, the specific table (or view, I forget) you're looking for is called OBJECT_DEPENDENCIES.

If you want to use it to find artifacts tied to scripted CalcViews, it can be slightly tricky... won't bother confusing you with the details unless this is your scenario..

Finally - a "hack" that many people would benefit from - turn on SQL Trace (if you know what you're doing) before navigating to the perspective/view/tab that you're using in HANA Studio that you want to investigate. After opening up the respective tab, turn off the SQL Trace, open the trace file - and dig around for a bit and you'll likely find useful SQL (or MDX) that the HANA Studio generates. In your case, you'd likely see SQL that HANA Studio generated to query OBJECT_DEPENDENCIES when you view the "Where-Used" tab.

[Former Member · Accepted solution]

Thanks for the response.

The "object_dependencies" view does help to find out the dependencies for tables/views. However, the view is not straight forward when it comes to querying the same for Stored Procedures.

Found another view "Procedure_Objects" in the 'HANA Monitor Views Guide". But I think it needs another System Stored Proc to be executed first to be able to view anything ("Contains results of the system procedure GET_PROCEDURE_OBJECTS").

However, I did find tracing pretty useful since I was able to extract the query which lists a huge dependency list for Stored Procedures as well. Interesting thing, the query even displays the indirectly dependent objects, which is great.

Not sure if am allowed to paste the query here, but for the benefit of others, here's what I did. Enable SQL trace, right click on the Stored Procedure and click on "Show Authorization". This displays a pretty tree-like graph and the query can be extracted from the generated Trace.

[Former Member · Accepted solution]

Cool. Glad you found it useful.

I've never had problems extracting out objects from procedures via OBJECT_DEPENDENCIES. Let me know what the specific issue is you're facing and I can try to help.

[Former Member · Accepted solution]

Yup. My bad. Works fine .

[former_member184969 · Accepted solution]

Thanks, Jody.

Can you help a bit more by explaining how HANA is managing the DEPENDENCY_TYPE in that table. I expected it to be some kind of depth indicator between objects ( direct =1, dependency at 2nd level = 2...) but the reality of data in my system are going against that hypothesis !

And then having a look at the reference documentation is just another disappointment...

I have to retro-engineer a good bunch of vews and tables, and I'm presently looking for the best (i.e. kickest) way of doing so.

Regards

[Former Member · Accepted solution]

This message was moderated.