This isn't really pretty - and I had to carve it out of a cursor that I use as part of my nightly processing - but it seems to be pretty accurate for calculating db space. Also, I can't claim to have come up with this. My inspiration came from a few MS-supplied stored procedures.
I guess that's as close to a disclaimer as I can get <g>. This will more than likely get wordwrapped so I hope you can make sense out of it...
Cheers, Ken
PS Replies/comments are appreciated/welcome
DECLARE @dbname SYSNAME
SELECT @dbname = 'pubs'
EXEC('USE '+@dbname+'
DECLARE @DBSize DEC(15,0),
@LogSize DEC(15,0),
@BytesPerPage DEC(15,0),
@PagesPerMB DEC(15,0),
@FreeSize DEC(15,4),
@Growth INT,
@PrintStr VARCHAR(200)
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 @FreeSize =
@DBSize -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255)
)
SELECT @Growth = MIN(growth)
FROM sysfiles
WHERE status & 0x40 <> 0x40
SELECT @PrintStr = " Space usage (Data + Log = Total)...: "+
LTRIM(STR((@DBSize) / @PagesPerMB,15,2))+" + "+
LTRIM(STR((@LogSize) / @PagesPerMB,15,2))+" = "+
LTRIM(STR((@DBSize+@LogSize) / @PagesPerMB,15,2))+" Mb"
PRINT @PrintStr
SELECT @PrintStr = " Free Data Space....................: "+
LTRIM(STR((@dbsize -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255)
)) / @PagesPerMB,15,2)+ " Mb")
+ " (" +
LTRIM(STR(((@FreeSize / @PagesPerMB)/(@DBSize / @PagesPerMB))*100,15,2))
+ "%)"+
CASE
WHEN (@FreeSize/@DBSize) < .05 AND @Growth = 0 THEN " ***** WARNING *****"
ELSE " "
END
PRINT @PrintStr
USE master
')