title: List all Applications, orchestrations if any, send ports and receive ports for BizTalk 2010?
body: <p>I need to list all Applications, orchestrations if any, send ports and receive ports for BizTalk 2010. I am trying to write a sql query and getting many duplicates. Can anyone help me?</p>
  
  <pre><code>SELECT
       APP.nvcName 
      ,APP.nvcName as [Application]
      ,A.nvcName AssemblyName
      ,O.nvcFullName OrchestrationFullName
      ,O.nvcName Orchestration
      ,'Send Port' as [PortType]
      ,S.nvcName as [PortName]
      ,S.bTwoWay as [SendType]
      ,'' --S.nvcName [ReceiveLocation]
      ,Adp.Name [AdapterType]
      ,RP.FullyQualifiedName [ReceivePipeline]
      ,SP.FullyQualifiedName [SendPipeline]
      ,S.nvcEncryptionCert [EncryptionCert]
  FROM dbo.bts_application as APP
  LEFT OUTER JOIN dbo.bts_sendport as S ON APP.nID = S.nApplicationID
  LEFT OUTER JOIN  dbo.bts_sendport_transport as ST ON S.nID = ST.nSendPortID
  LEFT OUTER JOIN  dbo.bts_assembly as A on A.nApplicationId = APP.nId
  LEFT OUTER JOIN  dbo.bts_orchestration as O ON O.nAssemblyID = A.nId
  LEFT OUTER JOIN  dbo.bts_orchestration_port AS OP ON OP.nOrchestrationID = O.nID
  LEFT OUTER JOIN  dbo.bts_orchestration_port_binding as OPB ON PB.nOrcPortID = OP.nID --and OPB.nSendPortID = S.nID
  LEFT OUTER JOIN  dbo.adm_Adapter as Adp ON ST.nTransportTypeId = Adp.Id
  LEFT OUTER JOIN  dbo.bts_pipeline as RP on RP.Id = S.nReceivePipelineId
  LEFT OUTER JOIN  dbo.bts_pipeline as SP on SP.Id = S.nSendPipelineId
  WHERE S.nApplicationID is not null AND Adp.Name IS NOT NULL
  </code></pre>