Tag Archives: Find Service Broker Object

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.


ServiceContract.name AS 'ContractName',

ServiQueue.name AS 'QueueName',

Servi.name AS 'ServiceName',

MessageType.name AS 'MessageType',





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


Here is the list I have received:


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

Reference: Ritesh Shah



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