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为不存在的数据库