SQL Server provides a stored proc, "'sp_spaceused", to return space info. But annoyingly and frustratingly, it returns two separate result sets.

For my use, I cloned their proc, adjusting it as needed for the functionality I wanted; that's the code below. Naturally adjust the table location and default bit settings as you prefer.

Then, when you need to get space info for a db, you can run the code using some variation of this:

EXEC userdb1.dbo.sp_spaceused_db 1 --change to your db name

EXEC userdb2.dbo.sp_spaceused_db 1 --change to your db name

EXEC ('SELECT * FROM tempdb.dbo.sp_spaceused_db')

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sp_spaceused_db]

@load_results_into_table bit = 0,

@delete_previous_rows_for_this_db bit = 1

AS

declare @pagesbigint-- Working variable for size calc.

,@dbname sysname

,@dbsize bigint

,@logsize bigint

,@reservedpages bigint

,@usedpages bigint

,@rowCount 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 @reservedpages = sum(a.total_pages),

@usedpages = sum(a.used_pages),

@pages = 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

)

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

/*

** Now calculate the summary data.

** reserved: sum(reserved) where indid in (0, 1, 255)

** data: sum(data_pages) + sum(text_used)

** index: sum(used) where indid in (0, 1, 255) - data

** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)

*/

select

database_name = db_name(),

database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))

* 8192 / 1048576,15,2)),

[unallocated space] = ltrim(str((case when @dbsize >= @reservedpages then

(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))

* 8192 / 1048576 else 0 end),15,2)),

reserved = ltrim(str(@reservedpages * 8192 / 1048576.,15,2)),

data = ltrim(str(@pages * 8192 / 1048576.,15,2)),

index_size = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,2)),

unused = ltrim(str((@reservedpages - @usedpages) * 8192/ 1048576.,15,2))

if @load_results_into_table = 1

begin

if OBJECT_ID('tempdb.dbo.sp_spaceused_db') IS NULL

CREATE TABLE tempdb.dbo.sp_spaceused_db (

ident int IDENTITY(1, 1) NOT NULL,

date_captured datetime NULL,

database_name sysname NULL,

database_size decimal(15, 2) NULL,

unallocated_space decimal(15, 2) NULL,

reserved decimal(15, 2) NULL,

data decimal(15, 2) NULL,

index_size decimal(15, 2) NULL,

unused decimal(15, 2) NULL

)

else

if @delete_previous_rows_for_this_db = 1

delete from tempdb.dbo.sp_spaceused_db

where database_name = DB_NAME()

insert into tempdb.dbo.sp_spaceused_db

select

GETDATE(),

database_name = db_name(),

database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))

* 8192 / 1048576,15,2)),

[unallocated space] = ltrim(str((case when @dbsize >= @reservedpages then

(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))

* 8192 / 1048576 else 0 end),15,2)),

reserved = ltrim(str(@reservedpages * 8192 / 1048576.,15,2)),

data = ltrim(str(@pages * 8192 / 1048576.,15,2)),

index_size = ltrim(str((@usedpages - @pages) * 8192 / 1048576.,15,2)),

unused = ltrim(str((@reservedpages - @usedpages) * 8192/ 1048576.,15,2))

end --if

RETURN

GO

EXEC sp_MS_marksystemobject 'sp_spaceused_db'

GO