Disabling auto-close in the database

If auto-close has been enabled in the database, it will have negative effects on the hard disk spinning load and also might flood the event log with "starting database..". It has been widely discussed in the Internet why it's not a good idea to enable it.

Anyway, here is the SQL script to disabled auto-close feature.

set nocount on

declare
    @seq int
    , @db sysname
    , @sql nvarchar(max)

select seq = row_number() over (order by name), name
into #db_list
from sys.databases
where is_auto_close_on = 1

set @seq = 0

while exists(
    select *
    from #db_list
    where seq > @seq
)
begin

    select top 1
        @seq = seq
        , @db = name
    from #db_list
    where seq > @seq
    order by seq

    print 'reseting auto close for ' + @db   

    set @sql = 'alter database ' + @db + ' set AUTO_CLOSE off'

    ----print @sql
    exec sp_executesql @sql

end

print 'done'

drop table #db_list

set nocount off