Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

After reading one of my recent article regarding “Small demonstration of Service broker for beginners”, one of the blog reader has asked me very interesting question. He asked me that though he has created all queues, message types etc. demonstrated in “Small demonstration of Service broker for beginners” article but how can he see list of all objects related to the particular contract? Is there any DMV which gives all these information?

I have created one small SELECT script based on the following system catalog view which caters the need of the question given above.

  • sys.service_contract_message_usages
  • sys.service_message_types
  • sys.service_contracts
  • sys.service_contract_usages
  • sys.services
  • sys.service_queue_usages
  • sys.service_queues

Here is the query I have created based on all the system catalog view given above.


SELECT

ServiceContract.name AS 'ContractName',

ServiQueue.name AS 'QueueName',

Servi.name AS 'ServiceName',

MessageType.name AS 'MessageType',

MessageUsage.is_sent_by_initiator,

MessageUsage.is_sent_by_target,

MessageType.validation,

MessageType.validation_desc

FROM sys.service_contract_message_usages AS MessageUsage

INNER JOIN sys.service_message_types AS MessageType ON MessageUsage.message_type_id =MessageType.message_type_id

INNER JOIN sys.service_contracts AS ServiceContract ON ServiceContract.service_contract_id =MessageUsage.service_contract_id

INNER JOIN sys.service_contract_usages ServContractUse ON ServContractUse.service_contract_id =ServiceContract.service_contract_id

INNER JOIN sys.services AS Servi ON Servi.service_id=ServContractUse.service_id

INNER JOIN sys.service_queue_usages AS SerQueueUse ON SerQueueUse.service_id = Servi.service_id

INNER JOIN sys.service_queues AS ServiQueue ON ServiQueue.object_id=SerQueueUse.service_queue_id

--you can query your own contract name or even remove WHERE clause

--to see complete list of Service Broker objects in database

WHERE ServiceContract.name like '%Extreme%'

ORDER By MessageType,QueueName

GO

Here is the list I have received:

ServiceBrokerObjectList

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

5 thoughts on “Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

  1. Pingback: Get Transmission queue row count in Service Broker

  2. Pingback: Service Broker implementation between two tables in one database

  3. Pingback: Service Broker Queue Activation and Thread in SQL Server

  4. Pingback: Setup service broker between two databases in SQL Server.

Comments are closed.