Skip to Content

This document is prepared based on version HANA 1.0 SPS 05 revision 46.

In HANA studio we have the cool feature to find out where our Information view (Attribute/Analytic/Calculation) is used by right clicking the view and selecting “Where-Used”. It then shows type of view , Used-In which view, package name in a sub-window panel “Where-Used List”.

But there is no similar feature to find the same for tables in a schema we use in modeling

In this document we will come to know how we can get the list.

There is a schema called “SRK” which has many tables and I use two tables “EMPLOYEE” and “DEPT” for simplicity to model information views.

Advanced readers can skip step 1 as you were already aware of this.

Step 1:

First let us create the Attribute view (AT_DEPT) which uses only single table “DEPT”.

We create Analytic view (AN_USING_AT_DEPT) using the table “EMPLOYEE” in data foundation and use the Attribute view (AT_DEPT) in logical join.

Now let us use the feature “Where-Used” for Attribute view (AT_DEPT) by right clicking the view and selecting “Where-Used”. It shows the result as attribute view is used in analytic view which contains in package ‘srk’.

                  whereused1.JPG

Step 2:

Now we need to find where my particular table is used in which of the information views or procedures, etc.

We do not have similar where-Used functionality for tables. Let us try by just right click the table in the schema and we will not found.

                   whereused2.JPG

The alternate solution is to write SQL, querying from tables referring to repository.

Here is the simple SQL:

SELECT   BASE_SCHEMA_NAME, BASE_OBJECT_NAME,

         DEPENDENT_SCHEMA_NAME,DEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE

FROM     “SYS”.”OBJECT_DEPENDENCIES”

WHERE   BASE_SCHEMA_NAME = ‘SRK’           /*   <==  Schema Name    */

  AND   BASE_OBJECT_NAME = ‘DEPT’          /*   <==  Table Name     */

;

This query will show the result we are looking for and also shows whether any procedures, synonyms, etc are using our table.

    whereused3.JPG

Based on DEPENDENT_OBJECT_TYPE, we can say whether it is used by view/procedure/synonym etc., and DEPENDENT_OBJECT_NAME gives its corresponding name. Package name can found in the DEPENDENT_OBJECT_NAME itself.

That’s it we successfully got the where-used list for our specific tables

Might be in future release, we can expect this feature if someone can initiate this.

Thank You for your time.

Raj

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply