FWIW: Database Space Used (stored proc)

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO

CREATE PROCEDURE sp_dbspaceused

as

declare @.id int -- The object id of @.objname.
declare @.pages int -- Working variable for size calc.
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.logsize dec(15)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

/*
** If @.id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @.dbsize = Pages used
** @.bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @.dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
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 database_name = db_name(),
database_size =
ltrim(str((@.dbsize + @.logsize) / @.pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@.dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @.pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @.pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @.pages = @.pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @.pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GOI think this one is shorter ;):

select
reserved=(
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))*8|||Yes it is. No one ever accused me of having an overabundance of imagination.

Thanks for the nice re-write.

Regards,

hmscott|||With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

Regards,

hmscott

ALTER PROC sp_dbSpaceUsed

AS

CREATE TABLE #TempSpace (
[Database] varchar(255),
Reserved dec(15),
Index_Size dec(15),
Data dec(15),
Unused dec(15)
)

DECLARE @.sSQL varchar(1000)

SELECT @.sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
SELECT
''?'' as [Database],
reserved=(
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255)))*8'

EXEC sp_MSforeachdb @.command1=@.sSQL

SELECT * FROM #TempSpace

DROP TABLE #TempSpace