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

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !