0
0
During this post we will discuss a requirement implemented by Table function: GET_OBJECT_REFERENCES. The GET_OBJECT_REFERENCES table function, returns a list of objects that a specified object references. Input is currently limits to the name of a view. As per the ask by business we need to identify the number of views in current Database. Along with this we need to find all dependent tables on which VIEWS is created. In addition to this need to calculate the Count for each entity i.e. VIEW count and dependent Tables count to be display in a single output.
So ideally the output should be display in the below format:
"Viewname": "view name is F_VIEW_E and view count is 10"
"Tablename": "Table name depends on view F_VIEW_E is X_TAB_A and Table count is 10"
"Tablename": "Table name depends on view F_VIEW_E is X_TAB_B and Table count is 21"
"Tablename": "Table name depends on view F_VIEW_E is X_TAB_C and Table count is 12"
"Tablename": "Table name depends on view F_VIEW_E is X_TAB_D and Table count is 20"
To implement, above scenario we have created a javascript procedure which traverses the INFORMATION_SCHEMA.VIEWS table to fetch all the Views details.
Based on the above View input, we call GET_OBJECT_REFERENCES function and pass the Database ,Schema and View Name. Finally store the all output in an Array and display it on the UI.
Technical Implementation:
Execute the Procedure in below way. Pass the Database name and the Schema for which we need to display the report.
CALL view_tbl_cnt(‘DEMO_DB’,’PUBLIC’);
As we can see in the above output, proc creates an Array and store all the details inside the array.
