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
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
Author And Source
この問題について(SQLSERVER 複数DBを一括AUTO_CLOSE offにする), 我々は、より多くの情報をここで見つけました https://qiita.com/mkawanee/items/8624bdf68fc033ef8a1e著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .