HANA Utility SQL List

Hi All,

I have seen lot of folks complaining about readily available utility views/tables for day to day activities …

Most of the information is available in the system tables but not at one place.. I thought I would come up with some utility sql’s that can help in day to day development/administration activities

Below are the compilation that would be helpful to developers..

Change List

select SRC_SYSTEM, CHANGE_NUMBER, PACKAGE_ID, OBJECT_NAME,OBJECT_SUFFIX
from _SYS_REPO.CHANGE_ENTRIES  WHERE CHANGE_NUMBER = '12345'

List of Sub Views and Tables Used in a final View

SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,COMMENTS,BASE_SCHEMA_NAME,
BASE_OBJECT_TYPE,DEPENDENCY_TYPE FROM SYS.OBJECT_DEPENDENCIES 
LEFT OUTER JOIN SYS.TABLES TABLEDESCR ON BASE_OBJECT_NAME = TABLEDESCR.TABLE_NAME 
AND BASE_SCHEMA_NAME = TABLEDESCR.SCHEMA_NAME 
WHERE DEPENDENT_OBJECT_NAME = 'TEST/CVG_TESTQUERY' --Package/ViewName
ORDER BY BASE_OBJECT_TYPE ASC

 

Check a view/dependent views for change number that need to be migrated(Open)

SELECT A.*,C.* FROM
( SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,COMMENTS,BASE_SCHEMA_NAME,BASE_OBJECT_TYPE,
DEPENDENCY_TYPE, SUBSTR_AFTER(BASE_OBJECT_NAME,'/') AS VIEW_CALCULATED,
SUBSTR_BEFORE(BASE_OBJECT_NAME,'/') AS PKG_CALCULATED
FROM SYS.OBJECT_DEPENDENCIES
LEFT OUTER JOIN
SYS.TABLES TABLEDESCR
ON BASE_OBJECT_NAME = TABLEDESCR.TABLE_NAME AND BASE_SCHEMA_NAME = TABLEDESCR.SCHEMA_NAME
WHERE DEPENDENT_OBJECT_NAME = 'Test/CVG_QUERY' and BASE_OBJECT_TYPE = 'VIEW') AS A
inner JOIN
(
SELECT A.* ,B.CREATED_BY, MAP(B.status,1,'OPEN',2,'RELEASED') as Status
FROM _SYS_REPO.CHANGE_ENTRIES AS A INNER JOIN
_sys_repo.changes as B ON A.CHANGE_NUMBER = B.change_number AND B.status = '1'
) AS C
ON A.VIEW_CALCULATED = C.OBJECT_NAME and A.PKG_CALCULATED = C.PACKAGE_ID

Check for the properties of a view

SELECT package_id, object_name, object_suffix, cdata, 
case when locate (cdata, 'enforceSqlExecution=true') > 0 then 'TRUE' else 'FALSE'
end as IS_SQLENGINE_FORCED,
CASE WHEN locate (cdata, 'checkAnalyticPrivileges=true') > 0 THEN 'TRUE' ELSE 'FALSE'
end as IS_AP_ENABLED
FROM _sys_repo.active_object
where object_suffix='calculationview' and object_name ='CVG_QUERY' AND PACKAGE_ID LIKE 'Test%';

List of Sub Views Used in a final View including AP’s

SELECT A.*,B.* FROM (SELECT DISTINCT DEPENDENT_OBJECT_NAME,BASE_OBJECT_NAME,-- COMMENTS, 
BASE_SCHEMA_NAME,BASE_OBJECT_TYPE,DEPENDENCY_TYPE,
SUBSTR_AFTER(BASE_OBJECT_NAME,'/') AS VIEW_CALCULATED,
SUBSTR_BEFORE(BASE_OBJECT_NAME,'/') AS VIEW_PACKAGE 
FROM SYS.OBJECT_DEPENDENCIES 
WHERE DEPENDENT_OBJECT_NAME = 'Test/CVG_QUERY' --Package/ViewName
and BASE_OBJECT_TYPE = 'VIEW' ) AS A 
left outer join ( SELECT package_id,object_name,object_suffix,cdata,
case when locate (cdata,'enforceSqlExecution=true') > 0  then 'TRUE'
else 'FALSE' end as IS_SQLENGINE_FORCED,
CASE WHEN locate (cdata,'checkAnalyticPrivileges=true') > 0 THEN 'TRUE'
ELSE 'FALSE' end as IS_AP_ENABLED 
FROM _sys_repo.active_object 
where object_suffix='calculationview'AND PACKAGE_ID LIKE 'Test%' ) --Package 
as B ON A. VIEW_CALCULATED = B.object_name AND A.VIEW_PACKAGE = B.PACKAGE_ID 

Check for the properties of a view -At Package level

select PACKAGE_ID,OBJECT_NAME,OBJECT_SUFFIX,VERSION_ID,ACTIVATED_AT,ACTIVATED_BY,EDIT,
CDATA,BDATA,COMPRESSION_TYPE,FORMAT_VERSION,DELIVERY_UNIT,DU_VERSION,DU_VENDOR,DU_VERSION_SP,
DU_VERSION_PATCH,OBJECT_STATUS,CHANGE_NUMBER,RELEASED_AT from _SYS_REPO.ACTIVE_OBJECT 
where CDATA like '%checkAnalyticPrivileges="true"%' and PACKAGE_ID like '%Test%'

In the next post I will come up with memory utility SQL’s for Memory and User Management

Thanks

Venkat