SQL SERVER: Enable RCSI on server databases matching a prefix

EnableRCSI.sql

/*==============================================================================
  Enable READ_COMMITTED_SNAPSHOT (RCSI) for all databases matching a prefix
  --------------------------------------------------------------------------
  - Dry-run by default (prints the ALTER statements, changes nothing)
  - Skips system DBs, snapshots, and any DB that is offline, read-only,
    in standby, or already has RCSI on (idempotent)
  - Requires EXCLUSIVE access per DB; @TerminationClause controls how it's
    obtained. ROLLBACK IMMEDIATE disconnects other sessions and rolls back
    their in-flight transactions.
==============================================================================*/
SET NOCOUNT ON;

DECLARE @Prefix            sysname       = N'...';            -- name prefix to match
DECLARE @DryRun            bit           = 1;                    -- 1 = print only, 0 = execute
DECLARE @TerminationClause nvarchar(100) = N'WITH ROLLBACK IMMEDIATE';
/*  @TerminationClause options:
      N'WITH ROLLBACK IMMEDIATE'         -- disconnect other sessions now (rolls back their tran)
      N'WITH ROLLBACK AFTER 30 SECONDS'  -- 30s grace period, then disconnect
      N'WITH NO_WAIT'                    -- abort this DB if it can't switch immediately
      N''                                -- no clause: wait/block until exclusive access is available
*/

DECLARE @db  sysname,
        @sql nvarchar(max),
        @msg nvarchar(400);

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT  name
    FROM    sys.databases
    WHERE   name LIKE @Prefix + N'%'
      AND   database_id > 4                     -- exclude master/tempdb/model/msdb
      AND   state_desc = N'ONLINE'              -- must be online to ALTER
      AND   is_read_only = 0                    -- can't ALTER a read-only DB
      AND   is_in_standby = 0                   -- skip log-shipping standbys
      AND   source_database_id IS NULL          -- skip database snapshots
      AND   is_read_committed_snapshot_on = 0   -- idempotent: skip already-enabled
    ORDER BY name;

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @db;

IF @@FETCH_STATUS <> 0
    RAISERROR('No matching databases need RCSI enabled (prefix = %s).', 0, 1, @Prefix) WITH NOWAIT;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER DATABASE ' + QUOTENAME(@db)
             + N' SET READ_COMMITTED_SNAPSHOT ON '
             + @TerminationClause + N';';

    IF @DryRun = 1
    BEGIN
        SET @msg = N'[DRY RUN] ' + @sql;
        RAISERROR('%s', 0, 1, @msg) WITH NOWAIT;
    END
    ELSE
    BEGIN
        SET @msg = N'Enabling RCSI on ' + QUOTENAME(@db) + N' ...';
        RAISERROR('%s', 0, 1, @msg) WITH NOWAIT;

        BEGIN TRY
            EXEC sys.sp_executesql @sql;
            RAISERROR('  -> done.', 0, 1) WITH NOWAIT;
        END TRY
        BEGIN CATCH
            SET @msg = N'  -> FAILED on ' + QUOTENAME(@db) + N': ' + ERROR_MESSAGE();
            RAISERROR('%s', 11, 1, @msg) WITH NOWAIT;
        END CATCH
    END

    FETCH NEXT FROM db_cur INTO @db;
END

CLOSE db_cur;
DEALLOCATE db_cur;


SELECT  name, [is_read_committed_snapshot_on]
FROM sys.databases
WHERE name LIKE @Prefix + N'%'