Print database_size from sp_spaceused

Question

Using SQL Server 2008, I have found that the database size can be obtained using

exec sp_spaceused

This procedure returns two tables:

database_name | database_size | unallocated space


reserved | data | index_size | unused

I would like to create a query where the only output of the SQL call is the value returned for database_size. I do not want any extraneous output (no table headers, column names, etc).

Is there a simple way to do this?

Answer 1

Another way to accomplish this without the creating database objects is to use the below query taken from the sp_spaceused stored procedure itself:

declare @dbsize bigint
declare @logsize 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 dbo.sysfiles

select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
* 8192 / 1048576,15,2) + ' MB') as Database_Size

Answer 2

Short answer: You can't do what you want with sp_spaceused.

Longer answer: You may be able to do what you want with sp_helpdb and creative use of temporary tables.
This bytes.com discussion includes one example which I've cleaned up and reproduced below:

create table dbo.DBs (
    dbname sysname,
    db_size varchar(20),
    owner sysname,
    dbid int,
    created datetime,
    status varchar(1000),
    compat int
)
go

insert into dbo.DBs
    exec sp_helpdb
go

select cast(replace(db_size, 'MB', '') as decimal(10,2))
       from dbo.DBs
       where dbname = 'SomeDatabase'
go