Complete One Line Space Used
One line results sp_spaceused script. Also includes:
DB Size= Data + DB Free Space + Transaction Log
DB Data Size= Data + DB Free Space with no TLog
DB Space Available= DB Free Space
All values are expressed in MB
declare @reserved dec(15,0)
declare @data1 dec(15,0)
declare @data2 dec(15,0)
declare @indexp dec(15,0)
declare @unused dec(15,0)
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @databasename varchar(30)
select @databasename = db_name()
DBCC UPDATEUSAGE (@databasename)
select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 <> 0)
select @bytesperpage = low from master.dbo.spt_values where number = 1 and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @reserved = sum(reserved) from sysindexes where indid in (0, 1, 255)
select @data1 = sum(dpages) from sysindexes where indid < 2
select @data2 = sum(used) from sysindexes where indid = 255
select @indexp = sum(used) from sysindexes where indid in (0, 1, 255)
select @unused = sum(reserved) - sum(used) from sysindexes where indid in (0, 1, 255)
select @databasename as 'DB Name',
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB') as 'DB Size',
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB') as 'DB Data Size',
ltrim(str((@dbsize - (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB') as 'DB Space Available',
ltrim(str((@reserved*8)/1024) + ' MB') as Reserved ,
ltrim(str(((@data1 + @data2)*8)/1024) + ' MB') as Data,
ltrim(str(((@indexp - (@data1+@data2))*8)/1024) + ' MB') as Indexes ,
ltrim(str((@unused*8)/1024) + ' MB') as 'Unused' 
