查看数据库下被激活存储过程的ServiceBroker信息

USE [master]
GO

/*****************************************************
Server:           N/A
DataBase:    master
Author:           SQLServer_2005
Object:           up_ProcBrokerInfo
Version:         1.0
CreateDate:  2006.09.22
Desc:             查询指定数据库下被激发存储过程
                        的ServiceBroker信息(用于Service Broker)
                        ,如果@DatabaseName为空，则查询所有数据库
******************************************************/

ALTER PROC [dbo].[up_ProcBrokerInfo]
@DatabaseName sysname      
AS
 SET NOCOUNT ON
 DECLARE @SQL nvarchar(max)
 SET @SQL = ''

    IF ISNULL(@DatabaseName,'') = ''
  BEGIN
   WITH cte_Database(name,database_id)
            AS(
    SELECT name,database_id
    FROM sys.databases
    WHERE name NOT IN('msdb','tempdb','model')
     )        
     SELECT @SQL = @SQL + N'SELECT A.spid,
                 A.procedure_name,                 
                       E.name AS UserName,                
                       B.name AS DatabaseName,            
                       C.name AS QueueName,              
                       C.create_date AS QueueCreateDate,  
                       C.max_readers AS QueueMaxReaders,  
                       D.name AS ServiceName              
          FROM sys.dm_broker_activated_tasks A
                          INNER JOIN sys.databases B
                           ON A.database_id = B.database_id
           INNER JOIN ' + name + N'.sys.service_queues C
                                          ON A.queue_id = C.object_id
           INNER JOIN ' + name + N'.sys.services D
               ON C.object_id = D.service_queue_id
           INNER JOIN ' +name + N'.sys.sysusers E
               ON A.execute_as = E.uid'
                                  + N' UNION ALL '
     FROM cte_Database
     ORDER BY database_id
        SET @SQL = RTRIM(SUBSTRING(@SQL,1,LEN(@SQL) - 10 ))     
  END
 ELSE
  BEGIN
   DECLARE @Count int
   SET @SQL = N'SELECT @Count = COUNT(1)
                        FROM sys.databases
                        WHERE name = ''' + @DatabaseName + N''''
   EXEC SP_EXECUTESQL @SQL,N'@Count int OUTPUT',@Count OUTPUT
   IF @Count = 0
   BEGIN
       PRINT 'The ''' + @DatabaseName + ''' is not valid object!'
    RETURN
   END
   SET @SQL = N'SELECT A.spid,                          
           A.procedure_name,                 
           E.name AS UserName,                
           B.name AS DatabaseName,            
           C.name AS QueueName,              
           C.create_date AS QueueCreateDate,  
           C.max_readers AS QueueMaxReaders,  
           D.name AS ServiceName              
          FROM sys.dm_broker_activated_tasks A
              INNER JOIN sys.databases B
               ON A.database_id = B.database_id
           INNER JOIN ' + @DatabaseName + N'.sys.service_queues C
            ON A.queue_id = C.object_id
           INNER JOIN ' + @DatabaseName + N'.sys.services D
            ON C.object_id = D.service_queue_id
           INNER JOIN ' + @DatabaseName + N'.sys.sysusers E
            ON A.execute_as = E.uid'
  END
  EXEC SP_EXECUTESQL @SQL
GO

--调用

EXEC up_ProcBrokerInfo ''
EXEC up_ProcBrokerInfo 'Demo_QC'  --Demo_QC为已经设置了Service Broker的数据库
EXEC up_ProcBrokerInfo 'Demo_QC1'  --Demo_QC1为不存在的数据库