DECLARE @db SYSNAME
DECLARE @sSQL NVARCHAR(2000)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT [name]
FROM master..sysdatabases
WHERE LOWER([name]) NOT IN ('master','pubs','msdb','tempdb','northwind') -- 'model' changed as well for all feature DBs to be OFF
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @sSQL = ('ALTER DATABASE ' + QUOTENAME(@db) + N' SET AUTO_CLOSE OFF')
EXEC sp_executesql @stmt = @sSQL
FETCH NEXT FROM db_cursor INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE @sSQL NVARCHAR(2000)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT [name]
FROM master..sysdatabases
WHERE LOWER([name]) NOT IN ('master','pubs','msdb','tempdb','northwind') -- 'model' changed as well for all feature DBs to be OFF
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @sSQL = ('ALTER DATABASE ' + QUOTENAME(@db) + N' SET AUTO_CLOSE OFF')
EXEC sp_executesql @stmt = @sSQL
FETCH NEXT FROM db_cursor INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
Enjoy!
J.