DECLARE @SearchString varchar(4000)

SET @SearchString = 'business'      -- < - - - - - -SEARCH STRING GOES HERE

set @SearchString = '%' + @SearchString + '%'

select 'Promptset' "Source", oh_name "Object Name", oh_client "Client", ' ' "Location", opse_name "Variable Name", opse_value "Value"
from uc4.dbo.opse
inner join uc4.dbo.oh on opse_oh_idnr = oh_idnr
where opse_value like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Override in JOB', oh_name, oh_client, opud_pname, opud_vname, opud_value
from uc4.dbo.opud
inner join uc4.dbo.oh on opud_oh_idnr = oh_idnr
where opud_value like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

-- CAN I SEARCH TASK ALIAS NAME HERE?
union
select distinct 
       'Alias name in ' + oh_name
     , jpp_object, oh_client, 'Task ' + ltrim(str(jpp_lnr)), '', jpp_alias
from uc4.dbo.jpp
inner join uc4.dbo.oh on jpp_oh_idnr = oh_idnr
where jpp_alias like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0
-- CAN I SEARCH TASK ALIAS NAME HERE?

union
select distinct 
       'Override in (' + oh_otype + ')' + oh_name + ' (Task' + ltrim(str(jppv_jpp_lnr)) + ')'  --"Schedule/WorkflowTask"
     , jpp_object, oh_client, jppv_pname, jppv_vname, jppv_value
from uc4.dbo.jppv
inner join uc4.dbo.oh on jppv_oh_idnr = oh_idnr
inner join uc4.dbo.jpp on jppv_oh_idnr = jpp_oh_idnr and  jppv_jpp_lnr = jpp_lnr
where jppv_value like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Variable Object', oh_name, oh_client, ' ', ovw_vvalue, ovw_value1
from uc4.dbo.ovw
inner join uc4.dbo.oh on ovw_oh_idnr = oh_idnr
where (ovw_value1 like @SearchString or ovw_vvalue like @SearchString)
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Call Notification', oh_name, oh_client, ' ', 'See Notification tab', ' '
from uc4.dbo.ooa
inner join uc4.dbo.oh on ooa_oh_idnr = oh_idnr
where ooa_text like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Login Object', oh_name, oh_client, ' ', olc_host, olc_login
from uc4.dbo.olc
inner join uc4.dbo.oh on olc_oh_idnr = oh_idnr
where (olc_host like @SearchString or olc_login like @SearchString)
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Variable Contents', oh_name, oh_client, ' ', ' ', isnull(ovd_directory,ovd_sql)
from uc4.dbo.ovd
inner join uc4.dbo.oh on ovd_oh_idnr = oh_idnr
where (ovd_directory like @SearchString or ovd_sql like @SearchString)
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 'Variable within Job', oh_name, oh_client, ' ', ov_vname, ov_value
from uc4.dbo.ov
inner join uc4.dbo.oh on ov_oh_idnr = oh_idnr
where ov_value like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select 
CASE CONVERT(VARCHAR,OT_Type)
WHEN 0 THEN 'Process Script'
WHEN 1 THEN 'Pre-Process Script'
WHEN 2 THEN 'Post-Process Script'
WHEN 3 THEN 'Child-Post-Process Script'
ELSE CONVERT(VARCHAR,OT_Type)
END "Script_Type",
a.OH_Name, oh_client, 'Line #' + cast(b.OT_Lnr as char),
a.OH_Title "Object_Title",
b.OT_Content
from uc4.dbo.OH a
inner join uc4.dbo.OT b on a.OH_IDNR=b.OT_OH_IDNR
where a.OH_DELETEFLAG=0 and a.OH_REFIDNR=0
and a.OH_Idnr > 100000
--and (a.OH_OTYPE='JOBS' or a.OH_OTYPE='CALL' or a.OH_OTYPE='INCL')
and b.OT_CONTENT like @SearchString

union
select 'Notification Recipent', oh_name, oh_client, '', '', ooi_emailaddress
from uc4.dbo.ooi
inner join uc4.dbo.oh on ooi_oh_idnr = oh_idnr
where ooi_emailaddress like @SearchString
and OH_DELETEFLAG=0 and OH_REFIDNR=0

union
select
CASE CONVERT(VARCHAR,JPOV_Location)
WHEN 1 THEN 'PreCondition'
WHEN 2 THEN 'PostCondition'
END "Source",
a.OH_Name, oh_client,
'task#' + convert(varchar,b.jpov_jpp_lnr) + ' line#' + convert(varchar,b.jpov_jppo_lnr) "Line_Number",
' ',
b.jpov_value "Script_Content"
from uc4.dbo.OH a
inner join uc4.dbo.JPOV b on a.OH_IDNR=b.JPOV_OH_IDNR
where a.OH_DELETEFLAG=0 and a.OH_REFIDNR=0
and b.JPOV_VALUE like @SearchString

union
select
'Documentation' "Source",
a.OH_Name, oh_client,
'',
b.ODOC_Name,
'See Documentation' "Script_Content"
from uc4.dbo.OH a
inner join uc4.dbo.ODOC b on a.OH_IDNR=b.ODOC_OH_IDNR
where a.OH_DELETEFLAG=0 and a.OH_REFIDNR=0
and cast(b.ODOC_Content as varchar(4096)) like @SearchString

union
select
'RA(' + b.JPPCV_VName + ')' "Source",
a.OH_Name, oh_client,
'',
'',
b.JPPCV_Value "Script_Content"
from uc4.dbo.OH a
inner join uc4.dbo.JPPCV b on a.OH_IDNR=b.JPPCV_OH_IDNR
where a.OH_DELETEFLAG=0 and a.OH_REFIDNR=0
and b.JPPCV_VALUE like @SearchString

union
select
'Working Directory' "Source",
a.OH_Name, oh_client,
'',
'',
b.JBA_Rest "Script_Content"
from uc4.dbo.OH a
inner join uc4.dbo.JBA b on a.OH_IDNR=b.JBA_OH_IDNR
where a.OH_DELETEFLAG=0
and a.OH_REFIDNR=0
and b.JBA_Rest like @SearchString