sp_spaceused alternative

gistfile1.sql

-- Returns the same results of MS SQL Server sp_spaceused, but in a single result set

		select 
			-- from first result set of 'exec sp_spacedused' 
			db_name() as [database_name]
			,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size]
			,ltrim(str((case when sf.dbsize >= pages.reservedpages then
				(convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages)) 
				* 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space]

			-- from second result set of 'exec sp_spacedused' 
			,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved]
			,ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data
			,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size
			,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused

			-- additional:
			,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB')  as dbsize
			,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB')  as logsize
		FROM (
			select 
				sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
				sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
			from dbo.sysfiles
		) sf,
		(
			select 
				sum(a.total_pages) as reservedpages,
				sum(a.used_pages) as usedpages,
				sum(
					CASE
						-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
						When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
						When a.type <> 1 Then a.used_pages
						When p.index_id < 2 Then a.data_pages
						Else 0
					END
				) as pages
			from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
			left join sys.internal_tables it on p.object_id = it.object_id
		) pages