Biztalk : T SQL script listing your biztalk artifacts (Orchestrations, Send Ports, Receive Ports/Location, Pipelines

USE BizTalkMgmtDb

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

ORDER BY 1,2,3

-- Status

-- 1 = Unenlisted

-- 2 = Stopped

-- 3 = Started