/*********************************************************************************************
Script Name: Sysfiles_Growth.txt
Purpose    : Displaying Auto-Growth factors of database files based on SYSFILES. 
Created On : October 31, 2005
Author     : Suresh Kumar Maganti
*********************************************************************************************/

set ansi_warnings off

declare @l_db_name       varchar(50)
       ,@l_sql_string    varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
 

declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
                         @l_db_name

While (@@fetch_status = 0)
begin
   select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
											       + 'else  convert (varchar, growth) + '' Percent''' + char(10)+char(13)
											 + 'end' + char(10)+char(13)
                         + 'from ' + @l_db_name + '.dbo.sysfiles'
 
   insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)   
   exec (@l_sql_string)

   fetch next from db_name_cursor into
                            @l_db_name
end
close db_name_cursor 
deallocate db_name_cursor 
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
go