USE BizTalkMsgBoxDb
SELECT Subscription.nvcApplicationName AS Application,
Subscription.nvcName AS Name,
EqualsPredicates.vtValue AS Subscription,
Subscription.uidInstanceID AS Instance
FROM Subscription WITH(NOLOCK)
INNER JOIN Services WITH(NOLOCK) ON Subscription.uidServiceID = Services.uidServiceID
INNER JOIN PredicateGroup WITH(NOLOCK) ON Subscription.uidPredicateGroupID = PredicateGroup.uidPredicateORGroupID
INNER JOIN EqualsPredicates WITH(NOLOCK) ON PredicateGroup.uidPredicateANDGroupID = EqualsPredicates.uidPredicateGroupID
ORDER BY 1, 2
Since this query only shows the so-called Equals predicates, it is far from complete but perhaps this query is helpfull to others as well.
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. Always use the WITH(NOLOCK) hint. Although possible it is not recommended to do updates to the BizTalk databases with self-created SQL queries.
