Suppose you want to find if a map is used on some ReceivePort. In BizTalk, one Receive Port can contain multiple maps. The one that is executed is based on the matching target namespace and root element.

<pre>
select app.nvcName as ApplicationName, 
       rp.nvcName as ReceivePortName,
       it.Name as MapName, 
       it.FullName,   -- you might have same map name in two different assemblies 
       bTwoWay 
  from bts_receiveport rp 
inner join bts_receiveport_transform rpt on rpt.nReceivePortID = rp.nID 
inner join bt_mapspec ms on ms.id = rpt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on rp.nApplicationID = app.nID 
where it.Name like '%204%'
order by rp.nvcName, it.Name 
</pre>

Now, here’s how to do the same for SendPorts.

<pre>
select app.nvcName as ApplicationName, 
       '' as ReceivePortName, 
       sp.nvcName as SendPortName,
       it.Name as MapName, 
       it.FullName,   -- you might have same map name in two different assemblies 
       bTwoWay 
  from bts_sendport sp 
inner join bts_sendport_transform spt on spt.nSendPortID = sp.nID 
inner join bt_mapspec ms on ms.id = spt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on sp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by sp.nvcName, it.Name 
order by 1, 2, 3
</pre>

Now we can get fancy and do both in one query using the “UNION” clause. This requires having the columns match, so I’ve added the dummy SendPort in the receive query, and the dummy ReceivePort in the SendPort Query. The sort has to then sort by numeric columns.

<pre>

select app.nvcName as ApplicationName, 
       rp.nvcName as ReceivePortName,
       '' as SendPortName, 
       it.Name as MapName, 
       it.FullName,   -- you might have same map name in two different assemblies 
       bTwoWay 
  from bts_receiveport rp 
inner join bts_receiveport_transform rpt on rpt.nReceivePortID = rp.nID 
inner join bt_mapspec ms on ms.id = rpt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on rp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by rp.nvcName, it.Name 
union 
select app.nvcName as ApplicationName, 
       '' as ReceivePortName, 
       sp.nvcName as SendPortName,
       it.Name as MapName, 
       it.FullName,   -- you might have same map name in two different assemblies 
       bTwoWay 
  from bts_sendport sp 
inner join bts_sendport_transform spt on spt.nSendPortID = sp.nID 
inner join bt_mapspec ms on ms.id = spt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on sp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by sp.nvcName, it.Name 
order by 1, 2, 3
</pre>