This is not a post about the pros and cons of READ_COMMITTED_SNAPSHOT, but is simply a script to set all databases within a server to use READ_COMMITTED_SNAPSHOT.
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
DECLARE @sql varchar(2000)
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @sql = 'ALTER DATABASE ' + @DATABASENAME + ' SET allow_snapshot_isolation ON'
Exec(@sql)
SELECT @sql = 'ALTER DATABASE ' + @DATABASENAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
Exec(@sql)
SELECT @sql = 'ALTER DATABASE ' + @DATABASENAME + ' SET read_committed_snapshot ON'
Exec(@sql)
SELECT @sql = 'ALTER DATABASE ' + @DATABASENAME + ' SET MULTI_USER'
Exec(@sql)
PRINT @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES
GO