info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools Services
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



List all Databases on an Instance with user created Service Broker Queues
by BF (Principal Consultant; Architecture; Engineering)
2021-07-17







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