Here's an Oracle SQL query that will list object uses by type.
-- List unused objects.sql
-- This SQL query will list object uses, or alternatively, list objects that
-- are not used anywhere in an Automation Engine system.
-- (c) Copyright 2020 Michael A. Lowry <michael.lowry@gmail.com>
--
-- Not includeded:
-- LOGINs
-- CONNs
-- VARAs in script commands other than :RESOLVE, :PUT_VAR, :PUT_VAR_COL, :DELETE_VAR, GET_VAR, PREP_PROCESS_VAR, RESOLVE_VAR
-- VARAs in other places (prompt set data sources, and MULTI VARAs, etc.)
-- VARAs in curly-brace-references (a LOT of work, because these can appear in dozens of different fields)
-- Probably not necessary after switching to case-insensitive REGEX-based comparisons:
--ALTER SESSION SET NLS_COMP=LINGUISTIC; -- Case insenstive matching
with Bind_Parms as
(select 100 as Client_Number from DUAL),
Object_Types as
(select 'JOBS' as Object_Type, 1 as Exec, 1 as Script, 1 as Agent from DUAL union all
select 'JOBF' as Object_Type, 1 as Exec, 1 as Script, 1 as Agent from DUAL union all
select 'SCRI' as Object_Type, 1 as Exec, 1 as Script, 0 as Agent from DUAL union all
select 'JOBP' as Object_Type, 1 as Exec, 1 as Script, 0 as Agent from DUAL union all
select 'EVNT' as Object_Type, 1 as Exec, 1 as Script, 1 as Agent from DUAL union all
select 'JSCH' as Object_Type, 1 as Exec, 1 as Script, 0 as Agent from DUAL union all
select 'CALL' as Object_Type, 1 as Exec, 0 as Script, 0 as Agent from DUAL union all
select 'JOBI' as Object_Type, 0 as Exec, 1 as Script, 0 as Agent from DUAL union all
select 'PRPT' as Object_Type, 0 as Exec, 0 as Script, 0 as Agent from DUAL union all
select 'SYNC' as Object_Type, 0 as Exec, 0 as Script, 0 as Agent from DUAL union all
select 'DOCU' as Object_Type, 0 as Exec, 0 as Script, 0 as Agent from DUAL union all
select 'FILTER' as Object_Type, 0 as Exec, 0 as Script, 0 as Agent from DUAL
),
Workflow_Tasks as
(select substr(OH_Name,1,3) as Prefix, OH_Name,OH_IDnr,JPP_Object,JPP_Lnr,JPP_Row,JPP_Col
from OH join JPP on OH_Idnr = JPP_OH_Idnr
where OH_DeleteFlag = 0
and JPP_OType not in ('<START>','<END>','<IF>','<FE>')
),
PRPT_Uses as
(select substr(OH_Parent.OH_Name,1,3) as Prefix, OH_Parent.OH_Name as Parent_Object, OH_PRPT.OH_Name as PromptSet
from OPU
join OH OH_Parent on OPU_OH_Idnr = OH_Parent.OH_Idnr
join OH OH_PRPT on OPU_Name = OH_PRPT.OH_Name
where OH_Parent.OH_DeleteFlag = 0
),
SYNC_Uses as
(select substr(OH_Parent.OH_Name,1,3) as Prefix, OH_Parent.OH_Name as Parent_Object, OH_SYNC.OH_Name as Sync
from OY
join OH OH_Parent on OY_OH_Idnr = OH_Parent.OH_Idnr
join OH OH_SYNC on OY_SyncName = OH_SYNC.OH_Name
where OH_Parent.OH_DeleteFlag = 0
),
FILTER_Uses as
(select substr(OH_Parent.OH_Name,1,3) as Prefix, OH_Parent.OH_Name as Parent_Object, OPPF_Lnr, OH_FILTER.OH_Name as Filter
from OPPF
join OH OH_Parent on OPPF_OH_Idnr = OH_Parent.OH_Idnr
join OH OH_FILTER on OPPF_Fltrname = OH_FILTER.OH_Name
--join OFC on OH_FILTER.OH_Idnr = OFC_OH_Idnr
--join OFA on OH_FILTER.OH_Idnr = OFA_OH_Idnr
where OH_Parent.OH_DeleteFlag = 0
),
Else_Executes as
(select substr(OH_Name,1,3) as Prefix, OH_Name, JPP_Lnr, JPP_WhenExecute as Object_Name, 'JPP_WhenExecute'
as Usage_Type from OH join JPP on OH_Idnr = JPP_OH_Idnr where OH_DeleteFlag = 0 and JPP_WhenExecute is not null
union all
select substr(OH_Name,1,3) as Prefix, OH_Name, JPP_Lnr, JPP_RExecute as Object_Name, 'JPP_RExecute'
as Usage_Type from OH join JPP on OH_Idnr = JPP_OH_Idnr where OH_DeleteFlag = 0 and JPP_RExecute is not null
union all
select substr(OH_Name,1,3) as Prefix, OH_Name, JPP_Lnr, JPP_MrtExecute as Object_Name, 'JPP_MrtExecute'
as Usage_Type from OH join JPP on OH_Idnr = JPP_OH_Idnr where OH_DeleteFlag = 0 and JPP_MrtExecute is not null
union all
select substr(OH_Name,1,3) as Prefix, OH_Name, JPP_Lnr, JPP_TcpExecute as Object_Name, 'JPP_TcpExecute'
as Usage_Type from OH join JPP on OH_Idnr = JPP_OH_Idnr where OH_DeleteFlag = 0 and JPP_TcpExecute is not null
union all
select substr(OH_Name,1,3) as Prefix, OH_Name, JPP_Lnr, JPP_ExtExecute as Object_Name, 'JPP_ExtExecute'
as Usage_Type from OH join JPP on OH_Idnr = JPP_OH_Idnr where OH_DeleteFlag = 0 and JPP_ExtExecute is not null
),
EXEC_VARAs as
(select substr(OH_Name,1,3) as Prefix, OH_Name, OVD_Source, OVD_Execute, OVD_VNameResult
from OH left join OVD on OH_IDnr = OVD_OH_Idnr
where OVD_Source = 'EXEC'
),
Task_Conditions as
(select substr(OH_Name,1,3) as Prefix, OH_Name,OH_IDnr,JPP_Object,JPP_Lnr,JPP_Row,JPP_Col,
JPPO_Type,JPOV_Type,JPPO_JPP_Lnr,JPPO_Location,JPPO_Lnr,JPPO_CarName,JPOV_VName,
cast(substr(JPOV_Value,1,3) as VARCHAR2(3))as EXEC_Pfx,
case when cast(substr(JPOV_Value,1,1) as VARCHAR2(200)) = '{'
then
cast(substr(JPOV_Value,2,instr(JPOV_Value,',')-2) as VARCHAR2(200))
else
cast(JPOV_Value as VARCHAR2(200))
end as JPOV_Value_VARCHAR
from OH, JPP, JPPO, JPOV
where OH_DeleteFlag = 0
and JPP_OH_Idnr = JPPO_OH_Idnr
and JPPO_JPP_Lnr = JPP_Lnr
and JPPO_OH_Idnr = OH_Idnr
and JPOV_OH_Idnr = OH_Idnr
and JPOV_JPPO_Lnr = JPPO_Lnr
and JPPO_Location = JPOV_Location
--and JPPO_Type = 'A' -- A: Actions C:Conditions
and JPOV_Type = 'V' -- V: Values?
and (
(JPPO_CarName in ('EXECUTE OBJECT','CLEAR VARIABLE','SET VARIABLE','SET_VALUE', 'USER DEFINED','CHECK ACTIVITIES','RESTART TASK') -- USER DEFINED, RESTART_TASK in curly-brace ref.
and JPOV_VName = 'XC_P01')
or (JPPO_CarName in ('USER DEFINED','PUBLISH VALUE') -- PUBLISH VALUE in curly-brace ref.
and JPOV_VName = 'XC_P02')
or (JPPO_CarName = 'CHECK ACTIVITIES'
and JPOV_VName = 'XC_P03')
)
and cast(JPOV_Value as VARCHAR2(200)) not like '##%'
),
INCLUDEs as
(select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *(INCLUDE|INC) *([''"]?) *([A-Z0-9\$@_\.#\-]{1,200})\3? *(, ?NOFOUND=IGNORE)? *(!.*)?$',1,1,'i',4) as VARCHAR2(200)) as INCLUDE_FirstArg
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *(INCLUDE|INC) .*$',1,1,0,'i') <> 0
order by OH_Name,OT_Lnr
),
ACTIVATE_UC_OBJECT_OBJs as
(select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *[^E]ACTIVATE_UC_OBJECT *\( *([''"]?) *(([A-Z0-9&\$@_\.#\-]{1,200})|(&\$?[A-Z][A-Z0-9_$@§#]{0,31})) *\1?[ ,\)].*$',1,1,'i',3) as VARCHAR2(200)) as AUO_FirstArg
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *[^E]ACTIVATE_UC_OBJECT',1,1,0,'i') <> 0
order by OH_Name,OT_Lnr
),
VARA_ForEachSouces as
(select substr(OH_Parent.OH_Name,1,3) as Prefix, OH_Parent.OH_Name as ForEach_Workflow, OH_VARA.OH_Name as ForEach_SourceVARA
from JPPF
join OH OH_Parent on JPPF_OH_Idnr = OH_Parent.OH_Idnr
join OH OH_VARA on JPPF_SourceName = OH_VARA.OH_Name
where OH_Parent.OH_DeleteFlag = 0
),
VARA_Script_Functions as
(select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *(GET_VAR|PREP_PROCESS_VAR) *\( *([''"]?) *(([A-Z0-9&\$@_\.#\-]{1,200})|(&\$?[A-Z][A-Z0-9_$@§#]{0,31})) *\2?[ ,\)].*$',1,1,'i',4) as VARCHAR2(200)) as VARA_Name
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *(GET_VAR|PREP_PROCESS_VAR) *\(',1,1,0,'i') <> 0
order by OH_Name,OT_Lnr
),
VARA_Script_Commands as
(select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *(PUT_VAR|PUT_VAR_COL) *([''"]?) *(([A-Z0-9&\$@_\.#\-]{1,200})|(&\$?[A-Z][A-Z0-9_$@§#]{0,31})) *\2?[ ,].*$',1,1,'i',4) as VARCHAR2(200)) as VARA_Name
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *(PUT_VAR|PUT_VAR_COL) ',1,1,0,'i') <> 0
order by OH_Name,OT_Lnr
),
VARA_Script_RESOLVE as (
select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *RESOLVE &\$?[A-Z][A-Z0-9_$@§#]{0,31} *= *([''"]?).*\{ *([''"]?) *(([A-Z0-9&\$@_\.#\-]{1,200})|(&\$?[A-Z][A-Z0-9_$@§#]{0,31})) *(,.*)? *\2 *} *\1.*$',1,1,'i',4) as VARCHAR2(200)) as VARA_Name
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *RESOLVE &\$?[A-Z][A-Z0-9_$@§#]{0,31} *=.*\{.*\}.*',1,1,0,'i') <> 0
union all
select substr(OH_Name,1,3) as Prefix, OH_Name, OH_IDnr, OT_Type, OT_Lnr, OT_Content,
CAST(REGEXP_SUBSTR(OT_Content,'^(!.*)?: *= *RESOLVE_VAR *\( *([''"]?).*\{ *([''"]?) *(([A-Z0-9&\$@_\.#\-]{1,200})|(&\$?[A-Z][A-Z0-9_$@§#]{0,31})) *(,.*)? *\2 *}.*\1 *\).*$',1,1,'i',4) as VARCHAR2(200)) as VARA_Name
from OH join OT on OH_IDnr = OT_OH_IDnr
where OH_DeleteFlag = 0
and REGEXP_INSTR(OT_Content,'^(!.*)?: *= *RESOLVE_VAR *\(.*\{.*\}.*\).*',1,1,0,'i') <> 0
order by OH_Name,OT_Lnr
),
Object_Uses as
(select Prefix, OH_Name as Parent_Object, JPP_Object as Used_Object, 'Workflow_Task' as Usage_Type from Workflow_Tasks
union all
select Prefix, OH_Name as Parent_Object, Object_Name as Used_Object, 'Else Execute' as Usage_Type from Else_Executes
union all
select Prefix, OH_Name as Parent_Object, OVD_Execute as Used_Object, 'EXEC VARA' as Usage_Type from EXEC_VARAs
union all
select Prefix, Parent_Object, PromptSet as Used_Object, 'PRPT' as Usage_Type from PRPT_Uses
union all
select Prefix, Parent_Object, Sync as Used_Object, 'SYNC' as Usage_Type from SYNC_Uses
union all
select Prefix, Parent_Object, Filter as Used_Object, 'FILTER' as Usage_Type from FILTER_Uses
union all
select Prefix, OH_Name as Parent_Object, JPOV_Value_VARCHAR as Used_Object, 'Task_Conditions' as Usage_Type from Task_Conditions
union all
select Prefix, OH_Name as Parent_Object, INCLUDE_FirstArg as Used_Object, 'INCLUDE' as Usage_Type from INCLUDEs
union all
select Prefix, OH_Name as Parent_Object, AUO_FirstArg as Used_Object, 'ACTIVATE_UC_OBJECT' as Usage_Type from ACTIVATE_UC_OBJECT_OBJs
union all
select Prefix, ForEach_Workflow as Parent_Object, ForEach_SourceVARA as Used_Object, 'ForEach_Source_VARA' as Usage_Type from VARA_ForEachSouces
union all
select Prefix, OH_Name as Parent_Object, VARA_Name as Used_Object, 'VARA_Script_Function' as Usage_Type from VARA_Script_Functions
union all
select Prefix, OH_Name as Parent_Object, VARA_Name as Used_Object, 'VARA_Script_Command' as Usage_Type from VARA_Script_Commands
union all
select Prefix, OH_Name as Parent_Object, VARA_Name as Used_Object, 'VARA_Script_RESOLVE' as Usage_Type from VARA_Script_RESOLVE
)
-- List object uses
--select * from Object_Uses
--where 1=1
--and Prefix = 'UC4'
--and rownum < 20000
--order by Prefix, Usage_Type, Parent_Object, Used_Object
-- List objects with no identifiable uses
select substr(OH_Name,1,3) as Prefix, OH_OType, OH_Name, OH_Title
from OH where 1=1
and OH_IDnr > 100000
and substr(OH_Name,1,4) = 'UC0.'
and OH_Client = (select Client_Number from Bind_Parms)
-- Exclude object types that are not typically used in other objects, or not yet included in Object_Uses
and OH_OType not in ('LOGIN','CONN','DOCU','JSCH')
and not exists (select 1 from Object_Uses where Used_Object=OH.OH_Name)
--and OH_Name not in (select nvl(Used_Object,'NULL') from Object_Uses)
and OH_DeleteFlag = 0
--and rownum < 20000
order by Prefix, OH_OType, OH_Name
Update 2020.01.29: I added support for
:INCLUDE statements and in the process significantly simplified the query by using
REGEXP_SUBSTR and
REGEXP_INSTR.
Update 2020.01.30: I added support PRPT, SYNC, and FILTER object references. I also added support for several types of VARA object reference, including ForEach workflow sources and VARA references appearing in scripts. Supported scripting commands/functions are
GET_VAR,
PREP_PROCESS_VAR,
PUT_VAR,
PUT_VAR_COL,
:RESOLVE, and
RESOLVE_VAR.
Update 2020.02.03: I updated the query to catch object references/uses in commented-out script lines. I also updated the Task_Conditions CTE to include object references in several types of condition and action.
(Note: the first three characters of all of our objects names is the so-called
prefix. This is just a naming convention. We use these first three characters to identify objects by project. This also facilitates authorization control.)