If you don't do an UPDATE STATISTICS you'll probably be dealing with GIGO, but you could use:
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb
, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb
FROM dbo.sysobjects AS so
JOIN dbo.sysindexes AS si
ON (si.id = so.id)
WHERE 'U' = so.type
GROUP BY so.name
ORDER BY so.name
SET NOCOUNT ON
CREATE TABLE #SpaceUsed (
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
DECLARE @tablename nvarchar(128)
, @maxtablename nvarchar(128)
, @cmd nvarchar(1000)
SELECT @tablename = ''
, @maxtablename = MAX(name)
WHILE @tablename < @maxtablename
SELECT @tablename = MIN(name)
WHERE xtype='u' and name > @tablename
SET @cmd='exec sp_spaceused['+@tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
SET NOCOUNT OFF
SELECT * FROM #SpaceUsed
DROP TABLE #SpaceUSed
Both, and text. The indid is what determines it.
indid = 0 = heap
indid = 1 = clustered index
indid = 255 = text/image
What I need is a way to subtract the nonclustered indexes, in the case that they happen to be on separate filegroups. Then I can get a script together to monitor space usage on a multi-filegroup system.
If you look at my original posting, the non-clustered indicies are what are reported as index_kb. The data pages are either the heap or the clustered index, and the blob (TEXT and IMAGE) pages are just that, the index_kb are what are left.
I can't be too certain, but I think that M$ drops all of the index pages into the reserved count for indids 0 and 1. For a test, I created a table, loaded a bit of data into it, ran dbcc updateusage(0), and ran both our scripts. Then create an index on the table, run the dbcc again, run both scripts, and see what changes. In the second instance, it looks like the data page count goes up for the table.
This sort of thing just makes me a little more jealous of the Oracle DBA, who can get all of his size statistics with a lot less back-bending.
Pat: I added a nonclustered index. The definitions of the columns reserved, used, and dpages in books online are almost infuriating. I don't think there is any way to derrive the number of reserved pages for a particular index on a separate filegroup from the data. I thought about just rounding the dpages value up to the next multiple of 8, but that does not take into account highly fragmented indexes. It may be that you have to run dbcc showcontig to get the actual values, but that is too resource intensive for a simple monitor.