SAP HANA: Extraction of Calculated Columns from a View/Package

[former_member407075]

Hi All,

Note  : Before going to actual details- I would like to thank @Roland Bouman for the Code . We have made very minor changes to suit our needs.

Just in case if you want to reference the original code from Roland Bouman - Please use below url

http://rpbouman.blogspot.com/2016/10/sap-hana-on-which-base-columns-do-my.html
https://github.com/just-bi/hades/tree/master/procedures

 

I was recently asked if there is a way to extract the list of all calculated columns in a view or package along with the data type of the calculated column and the logic used in the calculated column, the above code from @Roland was very helpful.

 

The entire code is broken down into 2 steps

Step1.Procedure to Parse the XML of the views  -

Please refer p_decode_xml_entities.sql in the above mentioned Git Hub

2.Read the Parsed XML and extract the calculated columns

You can place the Parsed XML code from above Git hub between DO BEGIN ... END Block

DO
BEGIN
p_parse_xml.sql  code from above GitHub
END

 

Now Lets test

Test1) Pass a single View -  View  is located at  system-local.private.Temp.perf/INPUT5

INPUT5 View



The View INPUT5 Contains another view INPUTDATE1 from the same package



Calculated Columns in INPUT5



Calculated Columns in INPUTDATE1



 

Now lets the Query  2)Read the Parsed XML and extract the calculated columns

This part have 2 important parameters - I will  pass the  package name and view name as default values

declare p_package_id nvarchar(255) default 'system-local.private.Temp.perf

declare p_object_name nvarchar(255) default 'INPUT5';

Now I  will  execute the 2 query with above parameters ( The PARSE XML Procedure is called in the second query )

 



Please note the query execution generated 3 result tables

 

Result Tab1:  Will give the list of views/sub views



 

Result Tab2:  Will give the list of Calculated Columns along with the corresponding view name - Each Calculated is divided in 3-4 rows



 

Result Tab3:  Will give the list of Calculated Columns 1 row per calculated column



 

 

 

Test2) Pass a package -   system-local.private.Temp.perf  

declare p_package_id nvarchar(255) default 'system-local.private.Temp.perf'; --'%';

declare p_object_name nvarchar(255) default '%';

This will consider all the views in the package



Query Results



 



List of calculated columns in the entire package



 

Also  special thanks to @Roland Bouman  for coming with the sql

Thank you all. Please provide feedback

 

Thanks

Venkat