Today, I was trying to find if a JSON pipeline we had was being used on a given BizTalk system.
I found the SQL command from here: https://gallery.technet.microsoft.com/Show-BizTalk-Applications-8f73d753
and just modified it to add an additional condition to the where clause

and rppl.Name like ‘%json%’

Here is the full command:

SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
orc.nvcName AS [Orchestration],
orc.nOrchestrationStatus AS [Status],
orp.nvcName AS [Orchestration Port],
pt.nvcName AS [Port Type],
pto.nvcName AS [Port Type Operation],
rp.nvcName AS [Receive Port],
rl.Name AS [Receive Location],
rppl.Name AS [Receive Pipeline],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
spg.nvcName AS [Send Port Group]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_orchestration orc WITH(NOLOCK) ON ass.nID = orc.nAssemblyId
LEFT OUTER JOIN bts_orchestration_port orp WITH(NOLOCK) ON orc.nID = orp.nOrchestrationId
LEFT OUTER JOIN bts_porttype pt WITH(NOLOCK) ON orp.nPortTypeID = pt.nID
LEFT OUTER JOIN bts_porttype_operation pto WITH(NOLOCK) ON pt.nID = pto.nPortTypeID
LEFT OUTER JOIN bts_orchestration_port_binding opp WITH(NOLOCK) ON orp.nID = opp.nOrcPortID
LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON opp.nReceivePortID = rp.nID
LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId
LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON opp.nSendPortID = sp.nID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
LEFT OUTER JOIN bts_sendportgroup spg WITH(NOLOCK) ON opp.nSpgID = spg.nID
WHERE orc.nvcName IS NOT NULL
and rppl.Name like '%json%'
ORDER BY 1,2,3

— Status
— 1 = Unenlisted
— 2 = Stopped
— 3 = Started

NOTE: – above only works with orchestrations

If you don’t have an orchestration involved, I have created another a pair of queries to find send and receive ports that use a given pipeline.