This script can be used to set below mentioned databases properties for all databases in a Instance.

- Autoclose for database must be FALSE
- Autoshrink for database must be FALSE
- Autogrowth for database file and log file must be in MB (not in percentage)
- For data file, the size for autogrowth is based on below mentioned condition:
If databas file size in MB/200<=10, Auto growth = 10 MB
If databas file size in MB/200>10 and <50, Auto growth = 50 MB
If databas file size in MB/200>50, Auto growth = 100 MB
*/

Declare @SrvName sysname
Declare @DBname sysname
Declare @Close int
Declare @Shrink int
Declare @Result table (Srvname Varchar(50), Dbname varchar(500) ,Fname varchar(500),Autoshrink varchar(50),Autoclose varchar(30),AutoGrowthDbFile Varchar(50),AutoGrowthLogFile Varchar(50) )
Declare @SQL Nvarchar(1000)
Declare @SQL2 Nvarchar(1000)
Declare @name sysname
Declare @size_MB Float
Declare @growth_MB Int
Declare @Type Varchar(20)
Declare @usage varchar (50)
Declare @sizeset nvarchar(100)

Set @SrvName=@@servername


Declare DBFilepm cursor for
select name , db_name(dbid) as dbname,
'size_MB' = convert (bigint, size) * 8/1024,
'growth_Mb' = (case status & 0x100000 when 0x100000 then growth else (convert (bigint, growth) * 8/1024) end),
'type' = (case status & 0x100000 when 0x100000 then '%' else ' MB' end) ,
'usage' = (case status & 0x40 when 0x40 then 'L' else 'D' end)
from master..sysaltfiles

Open DBFilepm
Fetch Next from DBFilepm into @name,@dbname,@size_MB,@growth_mb,@type,@usage
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into @result (SrvName , Dbname,Fname ) Values (@Srvname,@Dbname,@name)
If Rtrim(ltrim(@type))='%'

Begin
If Rtrim(Ltrim(@usage))='L'
Begin Try
Set @SQL2='Alter database [' + @dbname + '] modify file (name= [' + @name + '], filegrowth=10 MB)'
Exec sp_executesql @SQL2
Update @result Set AutoGrowthLogFile='Changed (Set to 10 MB)' where Fname=@name
End Try

Begin Catch
--Print 'File Growth settings for '+@name +' of database '+ @dbname + ' could not be changed error --received= ' + Error_Message()
Update @result Set AutoGrowthlogFile='Could not be changed' where Fname=@name
End Catch


IF @usage='D'
Begin
If @size_mb/200<=10

begin
Set @sizeset=10
End
If @size_mb/200>10 and @size_mb/200<50
Begin
Print @size_mb/200
Set @sizeset=50
End
If @size_mb/200>50
begin
Set @sizeset=100
End


Begin Try
Set @SQL2='Alter database [' + @dbname + '] modify file (name= [' + @name + '], filegrowth='+ @sizeset +' MB)'
Exec sp_executesql @SQL2
Update @result Set AutoGrowthdbFile='Changed (Set to '+ (@sizeset)+ 'MB)' where Fname=@name
End Try
Begin Catch
--Print 'File Growth settings for '+@name +' of database '+ @dbname + ' could not be changed error --received= ' + Error_Message()
Update @result Set AutoGrowthDbFile='Could not be changed' where fname=@name
End Catch
End
End

Else

Begin

If @usage='D'
Begin
Update @result Set AutoGrowthDbFile='OK' where Fname=@name
End
If @usage ='L'
Begin
Update @result Set AutoGrowthLogFile='OK' where Fname=@name
End
End


Fetch Next from DBFilepm into @name,@dbname,@size_mb,@growth_mb,@type,@usage
End
CLOSE DBFilepm
DEALLOCATE DBFilepm

Declare UpdateDBPm Cursor for


select name,is_auto_close_on,is_auto_shrink_on from sys.databases



OPEN UpdateDBPm
FETCH NEXT FROM UpdateDBPm INTO @DBname,@Close, @Shrink
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0
BEGIN

IF @Close=1
Begin
Begin Try
Set @SQL='ALTER DATABASE [' + @DBname + '] SET AUTO_CLOSE OFF WITH NO_WAIT'
Exec sp_executesql @SQL
Update @result Set AutoClose='Changed (Set to Off)' where Dbname=@dbname

End Try

Begin Catch

Update @result Set AutoClose='Counld not be changed' where Dbname=@dbname
End Catch

End
Else
Begin

Update @result Set Autoclose='OK' where Dbname=@dbname
End

IF @Shrink=1
Begin
Begin Try
Set @SQL='ALTER DATABASE [' + @DBname + '] SET AUTO_SHRINK OFF WITH NO_WAIT'
Exec sp_executesql @SQL
Update @result Set AutoShrink='Changed (Set to Off)' where Dbname=@dbname

End Try

Begin Catch

Update @result Set Autoshrink='Counld not be changed' where Dbname=@dbname

End Catch
End
Else
Begin

Update @result Set AutoShrink='OK' where Dbname=@dbname
End

FETCH NEXT FROM UpdateDBPm INTO @DBname,@Close, @Shrink
END
CLOSE UpdateDBPm
DEALLOCATE UpdateDBPm



Select Srvname,Dbname,Fname,Autoshrink,Autoclose,COALESCE(Autogrowthdbfile,'N/A') as Autogrowthdbfile,COALESCE(Autogrowthlogfile,'N/A') as AutogrowthLogFile from @result