create sql query to select all file shares used by all Biztalk applications from Biztalk database in a following format: Biztalk application,Location,file share path

Select * from (
--send ports 
SELECT
   nvcAddress  as path , btsapp.nvcName as BiztalkApp,sendport.nvcName as Port ,'SendPort'
FROM
    dbo.bts_application btsapp
INNER JOIN
    dbo.bts_sendport sendport ON btsapp.nID = sendport.nApplicationID
INNER JOIN
    dbo.bts_sendport_transport sendport_transport ON sendport.nID = sendport_transport.nSendPortID

Where nvcAddress like '%jack%'

UNION ALL

--receive locations
SELECT
     InboundTransportURL as path,
    btsapp.nvcName AS BiztalkApp,rp.nvcName ,
    'ReceivePort'
FROM
    dbo.bts_application btsapp
INNER JOIN
    dbo.bts_receiveport rp ON btsapp.nID = rp.nApplicationID
    INNER JOIN
    dbo.adm_ReceiveLocation rl ON rp.nID = rl.ReceivePortID

     Where InboundTransportURL like '%jack%'
) a
order by BiztalkApp

    --SELECT * FROM sys.tables where name like '%receive%'
    --SELECT * FROM adm_ReceiveLocation