set nocount on;

declare @db table (db varchar(128))
create  table #files
	(
	Drive varchar(10),
	DBName varchar(128),
	FileType varchar(10),
	LogicalName varchar(128),
	PhysicalName varchar(512),
	SizeMB numeric(10,2),
	Comment varchar(128),
	[FileGroup] varchar(128),
	SpaceUsedMB numeric(10,2),
	SpaceFreeMB numeric(10,2),
	[Status] varchar(50),
	MaxSizeMB varchar(20),
	Growth varchar(20),
	FileID smallint
	)
declare @d varchar(128)
declare @cmd varchar(4096)

insert into @db (db)
select name 
from sys.databases
where is_in_standby<>1
and DATABASEPROPERTYEX(name,'Status')='ONLINE'

while exists (select top 1 1 from @db)
	begin
		select top 1 @d=db from @db 

		set @cmd='use ['+@d+'];'
		set @cmd=@cmd+'select 
							left(physical_name,3) [Drive]
							,db_name() [DBName]
							,type_desc [FileType]
							,name [LogicalName]
							,physical_name
							,size/128. [SizeMB]
							,state_desc [comment]
							,case when data_space_id=0 then '''' 
							else (select name+N'' (''+type_desc+N'')'' collate SQL_Latin1_General_CP1_CI_AS from sys.data_spaces where data_space_id=df.data_space_id) end [FileGroup]

							,CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128. [SpaceUsedMB]
							,size/128.0 - CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128. AS [SpaceFreeMB]

							,df.state_desc as [Status]

							,case 
								when df.max_size=-1 then ''Unlimited''
								else convert(varchar(50),ceiling(df.max_size/128.)) end as [MaxSizeMB]

							,case 
								when df.is_percent_growth=1 then convert(varchar(20),df.growth)+''%''
								else 
								convert(varchar(20),convert(int,df.growth/128.))+'' MB'' end as [Growth]
							, df.file_id
						from sys.database_files df 
						
						option (recompile)'

		insert into #files (Drive,DBName,FileType,LogicalName,PhysicalName,SizeMB,Comment,[FileGroup],SpaceUsedMB,SpaceFreeMB,[Status],MaxSizeMB,Growth,FileID)
		exec (@cmd)

		set @cmd=''
		delete from @db where db=@d
	end

select 
	Drive
	,DBName
	,FileType
	,LogicalName
	,PhysicalName
	,SizeMB
	,Comment
	,[FileGroup]
	,SpaceUsedMB
	,SpaceFreeMB
	,[Status]
	,MaxSizeMB
	,Growth
	,FileID
from #files