SQL query to view all Instance Subscriptions and their expressions

From the Group Hub in BizTalk 2006 you can have a look at all existing subscriptions. However to have a look at the expression which is related to the instance, you have to open the Subscription Details screen and go to the Expression tab page.

At my current customer we needed to know which (and how many) subscriptions still exists from the previous version of our application, so we can take the appropriate action on them before migrating to the next version of our application.

Based on the BizTalk Server 2006 - Internals session, Lee Graber gave at TechEd 2006 in Barcelona, I took a backup from the Messagebox and Mangement database and in a offline situation, I did some investigation in the MessageBox-database tables which led to the following SQL query.

SELECT distinct
m.nvcName,
sub.dtTimeStamp,
sub.uidInstanceID,
ds.msgtype,
eq.vtValue
FROM Services s
INNER JOIN Modules m
ON s.nModuleID = m.nModuleID
INNER JOIN Subscription sub
INNER JOIN PredicateGroup pg
ON sub.uidPredicateGroupID = pg.uidPredicateORGroupID
INNER JOIN EqualsPredicates eq
ON pg.uidPredicateANDGroupID = eq.uidPredicateGroupID
ON s.uidServiceID = sub.uidServiceID
INNER JOIN BizTalkMgmtDB.dbo.bt_DocumentSpec ds
ON eq.uidPropID = ds.id
WHERE uidInstanceID is not null -- (not interested in the Activation subscriptions)
order by sub.dtTimeStamp

It is far from complete and I left out the matters which are only relevant to my customer (just some WHERE clauses), but perhaps this query is helpfull to others as well.
The Modules table contains the available BizTalk Applications, it relates to the Subcription table via the Services table.
Basically the Subscription part of the data model uses the following tables:

- Subscription
- PredicateGroup
- BitwiseANDPredicates
- EqualsPredicates
- EqualsPredicates2ndPass
- ExistsPredicates
- FirstPassPredicates
- GreaterThanOrEqualsPredicates
- GreaterThanPredicates
- LessThanOrEqualsPredicates
- LessThanPredicates
- NotEqualsPredicates

Depending of the operator used in the expression of the subscription, information is placed in the Predicate tables.

NOTE: take extreme care when querying the BizTalk database. Although possible it is not recommended to do updates to the BizTalk databases with self-created SQL queries.

Enjoy!