SQL Script for Service Broker to get Service Queue Details

Leave a comment (0) Go to comments

SQL Script to get Service Queue details, The following script will give us the following details related to aall services

  • Service Name
  • Queue Name
  • Max Readers
  • Activation Procedure
  • Contract Name
  • Sent by Initiator
  • Sent by target
  • Message Name
  • Validation Description
  • Xml Schema Column Name

SQL Script to get Service Details

/*-------------------------------------------------------------------------------------------------------------------------------Description    :     This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG

-- Author        :    DBATAG
-- Created on    :    22/09/2011
-- Version       :    1.0
-- Dependencies  :
--                        Table                            Procedure
-- Version Compatibility :    2005,2008, 2008 R2 and SQL Server Denali

-- Service Broker Overview 
SELECT SVC.name AS ServiceName 
      ,SQSCH.name + N'.' + SQ.name AS QueueName 
      ,SQ.max_readers AS MaxReaders 
      ,SQ.activation_procedure AS ActivationSP 
      ,SC.name AS ContractName 
      ,SCMU.is_sent_by_initiator AS SentByInit 
      ,SCMU.is_sent_by_target AS SentByTarget 
      ,SMT.name AS MessageName 
      ,SMT.validation_desc AS ValName 
      ,XSCSCH.name + N'.' + XSC.name AS XmlSchemaColName 
FROM sys.services AS SVC 
     INNER JOIN sys.service_queues AS SQ 
         ON SVC.service_queue_id = SQ.object_id 
     INNER JOIN sys.schemas AS SQSCH 
         ON SQ.schema_id = SQSCH.schema_id 
     LEFT JOIN sys.service_contract_usages AS SCU 
         ON SVC.service_id = SCU.service_id 
     LEFT JOIN sys.service_contracts AS SC 
         ON SCU.service_contract_id = SC.service_contract_id 
     LEFT JOIN sys.service_contract_message_usages AS SCMU 
         ON SC.service_contract_id = SCMU.service_contract_id 
     LEFT JOIN sys.service_message_types AS SMT 
         ON SCMU.message_type_id = SMT.message_type_id 
     LEFT JOIN sys.xml_schema_collections AS XSC 
         ON SMT.xml_collection_id = XSC.xml_collection_id 
     LEFT JOIN sys.schemas AS XSCSCH 
         ON XSC.schema_id = XSCSCH.schema_id 
ORDER BY ServiceName 

OUTPUT Screenshot

SQL Script for Service Broker to get Service Queue Details SQL Script for Service Broker

Note : To get more information about system catalogs related to Service Broker check BOL

EOF - SQL Script for Service Broker to get Service Queue Details, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.