A useful BizTalk Sql Query

Found this sql statement on another blog that shows you what ports are still referencing a mapping. This is useful if you need to undeploy the maps and you are having trouble finding what port might be still referencing.

select
'RcvPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_receiveport_transform rt
inner join bts_receiveport r
on rt.nReceivePortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id
--order by Port, nSequence

union

select
'SendPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_sendport_transform rt
inner join bts_sendport r
on rt.nSendPortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id

order by PortType, Port, nSequence