What is the Used, Free and Reserved space per DB / LOG

What is the Used, Free and Reserved space per DB / LOG

--****************************************************************************************
-- Used, Free and Reserved space per DB / LOG
--****************************************************************************************
-- Versie:  1.0
-- Author:  Theo Ekelmans
-- Date:    2009-06-11
--
--****************************************************************************************

declare	@sql_string nvarchar(4000)
declare	@DbName sysname 

set nocount on

create table #dbinfo
(
dbname sysname,
dbTotalFileSizeMB varchar(15),
dbDataFileSizeMB varchar(15),
dbLogFileSizeMB varchar(15),
dbFileSizeUnusedMB varchar(15),
--dbLogSizeUnusedMB varchar(15),
ReservedMB varchar(15),
DataMB varchar(15),          
IndexSizeMB varchar(15),     
UnusedMB varchar(15)
)

create table #temp_table(	database_name nvarchar(255), 
							log_size_MB numeric(17,2), 
							log_space_used numeric(17,2), 
							status char(10)
						) 

set @sql_string = 'dbcc sqlperf(logspace)'
insert into #temp_table 
execute sp_executesql @sql_string

declare dcur cursor local fast_forward
for 
select name from master..sysdatabases

open dcur
fetch next from dcur into @DbName
while @@fetch_status=0

begin

	set @sql_string = '	declare  @pages	bigint		
								,@dbsize bigint
								,@logsize bigint
								,@reservedpages  bigint
								,@usedpages  bigint

						select	 @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
								,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
						from	[' + @DbName + '].dbo.sysfiles

						select	@reservedpages = sum(a.total_pages),
								@usedpages = sum(a.used_pages),
								@pages = sum(	CASE
													-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
													When it.internal_type IN (202,204) Then 0
													When a.type <> 1 Then a.used_pages
													When p.index_id < 2 Then a.data_pages
													Else 0
												END
								)
						from	[' + @DbName + '].sys.partitions p 
									inner join [' + @DbName + '].sys.allocation_units a 
										on p.partition_id = a.container_id
									left join [' + @DbName + '].sys.internal_tables it 
											on p.object_id = it.object_id

						select 	 dbName = ''[' + @DbName + ']''
								,dbTotalFileSizeMB = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2))
								,dbDataFileSizeMB = ltrim(str((convert (dec (15,2),@dbsize) ) * 8192 / 1048576,15,2))
								,dbLogFileSizeMB = ltrim(str((convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2))
								,dbFileSizeUnusedMB = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) )
								,ReservedMB = ltrim(str(@reservedpages * 8192 / 1048576.,15,0))
								,DataMB = ltrim(str(@pages * 8192 / 1048576.,15,0))
								,IndexSizeMB = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,0)) 
								,UnusedMB = ltrim(str((@reservedpages - @usedpages) * 8192 / 1048576.,15,0))		
	'

	insert into #dbinfo(dbName, dbTotalFileSizeMB, dbDataFileSizeMB, dbLogFileSizeMB, dbFileSizeUnusedMB, ReservedMB, DataMB, IndexSizeMB, UnusedMB) 
	execute sp_executesql @sql_string


	fetch next from dcur into @DbName
end
close dcur
deallocate dcur

Select * 
from #dbinfo 
left join #temp_table
on dbname = '[' + database_name + ']'
order by dbname asc

drop table #dbinfo, #temp_table