How-to: Use BizTalkMgmtDb to get referenced assemblies

Sometimes when you are trying to remove an assembly from within the Administration Console you get and error saying that it’s being used. Most often you’ll get information on where it’s being used at the same time, but not always. These queries are for those times. With the help of these queries, and the knowledge gained from working with these tables you could also lookup things like assemblies that aren’t being used or on which ports a particular map or pipeline is used – information that is not easily accesible through the Administration Console.

Get Assemblies referenced by Maps on ReceivePorts

Select    ass.nvcName as Assembly,

        itm.Name as Map,

        rcv.nvcName as ReceivePort

from    bts_receiveport_transform tr

join    bt_MapSpec map on tr.uidTransformGUID = map.id

join    bts_item itm on map.itemid = itm.id

join    bts_assembly ass on map.assemblyid = ass.nID

join    bts_receiveport rcv on rcv.nID = tr.nReceivePortID

order by Assembly, Map, ReceivePort

Get Assemblies referenced by Maps on SendPorts

Select    ass.nvcName as Assembly,

        itm.Name as Map,

        snd.nvcName as SendPort

from    bts_sendport_transform tr

join    bt_MapSpec map on tr.uidTransformGUID = map.id

join    bts_item itm on map.itemid = itm.id

join    bts_assembly ass on map.assemblyid = ass.nID

join    bts_sendport snd on snd.nID = tr.nSendPortID

order by Assembly, Map, SendPort

Get Assemblies referenced by Pipelines on ReceiveLocations

select    ass.nvcName as Assembly,

        pipe.Name as Pipeline,

        loc.Name as ReceiveLocation

from    adm_receiveLocation loc

join    bts_pipeline pipe on pipe.ID = loc.ReceivePipelineId

join    bts_assembly ass on ass.nID = nAssemblyID

Get Assemblies referenced by Pipelines on SendPorts

select    ass.nvcName as Assembly,

        pipe.Name as Pipeline,

        snd.nvcName as SendPort

from    bts_sendport snd

join    bts_pipeline pipe on pipe.ID = snd.nSendPipelineId

join    bts_assembly ass on ass.nID = nAssemblyID