Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
2,060
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
1 Comment