List all Databases on an Instance with user created Service Broker Queues
DECLARE @DB varchar(150) = ''
CREATE TABLE #TMPInstanceServiceBroker
(
DatabaseName varchar(150), ServiceBrokerQueueName varchar(500), CreatedDate datetime, ModifyDate datetime,
IsActivationEnabled bit, ActivationStoredProcedure varchar(500)
)
SELECT QUOTENAME(NAME) as [Name] INTO #TMP FROM sys.databases
WHERE state = 0
AND database_id > 4
ORDER BY [NAME] ASC;
WHILE EXISTS (SELECT 1 from #TMP)
BEGIN
SET @DB = (SELECT TOP 1 [NAME] FROM #TMP)
DECLARE @cmd NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX) = N'';
SELECT @cmd +=
N'
USE ' + @DB + '
If Exists
(
Select DB_Name() as [Database], Name as [Service Broker Queue Name], Create_Date, Modify_Date, Is_Activation_Enabled, Activation_Procedure from ' + @DB + '.sys.service_queues where is_ms_shipped = 0
)
Insert into #TMPInstanceServiceBroker
Select DB_Name() as [Database], Name as [Service Broker Queue Name], Create_Date, Modify_Date, Is_Activation_Enabled, Activation_Procedure from ' + @DB + '.sys.service_queues where is_ms_shipped = 0
'
EXEC sp_executesql @cmd, N'@sql NVARCHAR(MAX) OUTPUT', @sql OUTPUT;
SET @sql = STUFF(@sql, 1, 10, '') + N' ORDER BY TabName;';
PRINT @sql;
-- EXEC sp_executesql @sql;
DELETE FROM #TMP WHERE [NAME] = @DB
END
DROP TABLE #TMP
SELECT @@SERVERNAME as 'InstanceName', * FROM #TMPInstanceServiceBroker Order By DatabaseName ASC, ServiceBrokerQueueName ASC
DROP TABLE #TMPInstanceServiceBroker
--SELECT * FROM [ServicesEventTransaction].sys.service_queues
|
|
|
|
|
|