If object_id (' Pr_modify_file_add ') is not null
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 isn't 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 isn't 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 output ', @re output
exec pr_modify_file_add_log @databasename, @re
FETCH NEXT from Cur01 into @databasename
End
Close Cur01
Deallocate Cur01
Go
See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/