Enable broker service on all databases but it check if Service Broker is enabled.

 1
 2-- ***************************************** --
 3--       SQL Script to ENABLE_BROKER
 4-- ***************************************** --
 5--
 6PRINT @@servername;
 7PRINT Getdate();
 8BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
 9
10--#region Select databases to Enable Broker Service
11
12SELECT [name] INTO #dbs
13FROM sys.databases
14WHERE
15    database_id > 4
16    AND is_broker_enabled = 0  -- check if Service Broker is enabled.
17    AND name NOT IN ('ReportServer','ReportServerTempDB')
18
19--#endregion Select databases to Enable Broker Service
20
21USE [MASTER];
22SET NOCOUNT ON;
23DECLARE @name nvarchar(200);
24DECLARE @sql NVARCHAR(MAX);
25DECLARE @ToKill AS NVARCHAR(MAX);
26
27WHILE (EXISTS(SELECT * FROM #dbs))
28BEGIN
29
30    SELECT TOP 1 @name = name
31    FROM #dbs;
32    DELETE FROM #dbs
33    WHERE
34        @name = name;
35
36    DECLARE @msg nvarchar(200) = '-- SET ENABLE_BROKER Database : ' + @name;
37    RAISERROR (@msg, 10, 1) WITH NOWAIT;
38
39    SET @ToKill = '';
40    SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
41    FROM master..sysprocesses
42    WHERE dbid = DB_ID(@name);
43
44    RAISERROR (@tokill, 10, 1) WITH NOWAIT;
45    EXEC (@tokill);
46
47    SET @sql = 'ALTER DATABASE [' + @name + '] SET ENABLE_BROKER WITH NO_WAIT;'
48
49    RAISERROR (@sql, 10, 1) WITH NOWAIT;
50    EXEC (@sql);
51
52END