SQLSERVER 複数DBを一括AUTO_CLOSE offにする

db_auto_close.sql

declare @tbl table (
  rowid int IDENTITY(1,1), 
  dname varchar(100)
)
declare @i int, @max int, @dname varchar(100), @SQL VARCHAR(1000);

insert into @tbl(dname)
select a.name
from master.sys.databases as a
WHERE a.name NOT IN ('master', 'tempdb', 'model', 'msdb')
 and a.name not like 'ReportServer%'
 and a.is_auto_close_on = 1

set @i = 1
select @max = max(rowid) from @tbl

WHILE (@i <= @max)
BEGIN
  select @dname = a.dname
  from @tbl as a
  where a.rowid = @i

  SET @SQL = 'ALTER DATABASE ' + @dname + ' set AUTO_CLOSE off';
  exec(@SQL);

  set @i += 1

end