SELECT CONVERT(numeric(10, 2), ROUND(size / 128., 2)) AS 'File Size (MB)', CONVERT(numeric(10, 2), ROUND(FILEPROPERTY(name, 'SpaceUsed') / 128., 2))
AS 'Used Space (MB)', CONVERT(numeric(10, 2), ROUND((size - FILEPROPERTY(name, 'SpaceUsed')) / 128., 2)) AS 'Unused Space (MB)', name AS 'FileName',
filename AS filenamePath, status, perf, growth, maxsize, groupid
FROM sys.sysfiles AS a

declare @dbName sysname
,@vSQL varchar(4000)

create table #tblDBSpaceUsage
(
DBName sysname,
Reserved varchar(250),
DataSize varchar(250),
IndexSize varchar(250),
Unused varchar(250),

)

declare cur_ChkSpace cursor for select name from master..sysdatabases order by name

open cur_ChkSpace

fetch cur_ChkSpace into @dbname



while @@fetch_status = 0
begin


set @vSQL = 'select ''' + @dbName + ''', str((sum(a.total_pages)*8192)/1024.,15,0) Reserved,'
set @vSQL = @vSQL + 'str((sum('
set @vSQL = @vSQL + ' CASE'
set @vSQL = @vSQL + ' When it.internal_type IN (202,204) Then 0'
set @vSQL = @vSQL + ' When a.type <> 1 Then a.used_pages'
set @vSQL = @vSQL + ' When p.index_id < 2 Then a.data_pages'
set @vSQL = @vSQL + ' Else 0'
set @vSQL = @vSQL + ' END'
set @vSQL = @vSQL + ' )*8192)/1024.,15,0) Data,'

set @vSQL = @vSQL + ' str((sum(a.used_pages) - sum('
set @vSQL = @vSQL + ' CASE'
set @vSQL = @vSQL + ' When it.internal_type IN (202,204) Then 0'
set @vSQL = @vSQL + ' When a.type <> 1 Then a.used_pages'
set @vSQL = @vSQL + ' When p.index_id < 2 Then a.data_pages'
set @vSQL = @vSQL + ' Else 0'
set @vSQL = @vSQL + ' END'
set @vSQL = @vSQL + ' )) * 8192 / 1024.,15,0) Index_Size,'
set @vSQL = @vSQL + ' str((sum(a.total_pages) - sum(a.used_pages)) * 8192 / 1024.,15,0) Unused'
set @vSQL = @vSQL + ' from ' + @dbName + '.sys.partitions p join ' + @dbName + '.sys.allocation_units a on p.partition_id = a.container_id'
set @vSQL = @vSQL + ' left join ' + @dbName + '.sys.internal_tables it on p.object_id = it.object_id'

insert into #tblDBSpaceUsage exec (@vSQL)


fetch cur_ChkSpace into @dbname

end
close cur_ChkSpace
deallocate cur_ChkSpace

select DBName
,Reserved 'Reserved (KB)'
,DataSize 'DataSize (KB)'
,IndexSize 'IndexSize (KB)'
,Unused 'Unused (KB)'
from #tblDBSpaceUsage

drop table #tblDBSpaceUsage