Here’s a sql script I use against the BizTalkMgmtDb when I’m debugging issues that I think are caused by botched deployments:


SELECT nvcName AS AssemblyName, nvcVersion AS Version, DATEADD(hh, DATEDIFF(hh, GetUTCDate(), GETDATE()), dtDateModified) AS DateModified, nvcModifiedBy AS ModifiedBy
FROM bts_assembly
WHERE (nSystemAssembly = 0)
ORDER BY AssemblyName


It returns the assembly name, version, the date it was modified (deployed) and who deployed it.  The DATEADD stuff is to put the dates into the appropriate time zone.


Here’s a similiar script but it focuses on orchestrations:


SELECT nvcFullName as OrchestrationName, DATEADD(hh, DATEDIFF(hh, GetUTCDate(), GETDATE()), dtModified) as DateModified
FROM dbo.bts_orchestration