SQL Server script to display files for autogrowth SQL Server Tips by Burleson Consulting

*********************************************************
-- up_bn_storage_files
*********************************************************
From filegroups, the DBA can drill down to the individual file 
level with the up_bn_storage_files procedure in SQL Server 2000 
and above. This script will reveal whether any files are not 
enabled for autogrowth and if any file has a maximum file limit 
imposed. 

IF OBJECT_ID('dbo.up_bn_storage_files') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_bn_storage_files
IF OBJECT_ID('dbo.up_bn_storage_files') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_bn_storage_files >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.up_bn_storage_files >>>'
END
go
CREATE PROCEDURE up_bn_storage_files
AS
set nocount on

declare @db_name sysname,
@testamt float

create table #file_info
(dbname sysname NULL,
groupid int NULL,
groupname sysname NULL,
logicname nchar(128) NULL,
filename nchar(260) NULL,
filestatus int NULL, /* to get data or log */
file_size float NULL,
file_max_size float NULL,
file_growth_size float NULL)

declare 
db_cursor cursor for
select 
name
from 
            master..sysdatabases
See code depot for full script
where 
databasepropertyex(name,'status') not in 
('SUSPECT', 'OFFLINE', 'RESTORING', 'RECOVERING') and 
has_dbaccess(name) = 1

open db_cursor
fetch 
   db_cursor 
into 
@db_name

while @@fetch_status = 0
begin
insert 
into #file_info
(dbname,
groupid,
groupname,
logicname,
filename,
    filestatus,
file_size,
file_max_size,
file_growth_size)
exec 
('use [' + @db_name + '] 
select 
db_name = db_name(),
b.groupid,
a.groupname,
b.name,
b.filename,
b.status,
b.size,
b.maxsize,
b.growth 
from 
sysfilegroups a, 
sysfiles b 
  where 
a.groupid =* b.groupid')

fetch 
db_cursor 
into 
@db_name
end

select 
dbname,
logicname,
file_group = case groupname
when NULL then 'LOG'
      else groupname
end,
filename,
size_in_mb = convert(decimal(17,2),((file_size * 8) / 1024)),
can_grow = case file_growth_size
when 0 then 'NO'
else 'YES'
end,
growth_amount = case (filestatus&0x100000)
when 0x100000 then convert(varchar(18),file_growth_size) + '%'
else convert(varchar(18),(file_growth_size * 8) / 1024) + ' MB'
end,
max_file_size_mb = case(file_max_size)
  when -1 then 'UNLIMITED'
else convert(varchar(18),((file_max_size * 8) / 1024))
end
from
#file_info
order by 
1

deallocate db_cursor
drop table #file_info
go
IF OBJECT_ID('up_bn_storage_files') IS NOT NULL
PRINT '<<< CREATED PROCEDURE up_bn_storage_files >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE up_bn_storage_files >>>'
go

< up_bn_storage_files (database cursor code segment)
declare db_cursor cursor for
select name
   from master..sysdatabases
where databaseproperty(name,N'IsShutdown') <> 1 and
databaseproperty(name,N'IsInRecovery') <> 1 and
databaseproperty(name,N'IsNotRecovered') <> 1 and 
databaseproperty(name,N'IsOffline') <> 1 and
databaseproperty(name,N'IsSuspect') <> 1 and 
has_dbaccess(name) = 1