sql server批量修改用户数据库自动增长 - MSSQL教程

drop proc pr_modify_file_add

go

create proc pr_modify_file_add

@in_name nvarchar(256)

as

declare @d_name varchar(20)

set @d_name=@in_name

execute

(

'alter database '+@d_name+' modify file(name='+@d_name+', filegrowth=200MB) '

)

go

declare @databasename nvarchar(256)

declare cur01 cursor for

select name from sys.databases

where name not in ('master','tempdb','model','msdb','reportserver',& #39;ReportServerTempDB')

open cur01

fetch next from cur01 into @databasename

while @@fetch_status=0

begin

exec pr_modify_file_add @databasename

fetch next from cur01 into @databasename

end

close cur01

deallocate cur01

go

if object_id('pr_modify_file_add_log') is not null

drop proc pr_modify_file_add_log

go

create proc pr_modify_file_add_log

@in_name nvarchar (256),

@dlog nvarchar(256)

as

declare @d_name nvarchar(20)

set @d_name=@in_name

execute

(

'alter database '+@d_name+' modify  file (name='+@dlog+', filegrowth=400MB)'

)

go

declare @databasename nvarchar(256)

declare @string nvarchar(256)

declare @re nvarchar(256)

declare cur01 cursor for

select name from sys.databases

where name not in ('master','tempdb','model','msdb','reportserver',& #39;ReportServerTempDB')

open cur01

fetch next from cur01 into @databasename

while @@fetch_status=0

begin

set @string='select @a=name from '+@databasename +'.sys.database_files where type=1'

exec sp_executesql @string, N'@a nvarchar(200) output',@re output

exec pr_modify_file_add_log @databasename,@re

fetch next from cur01 into @databasename

end

close cur01

deallocate cur01

go

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!