Hi,

Thanks for this article, it was very helpful in explaining how space is reported in the different columns of the sysindexes table, but I am left wondering why we would want to have so many separate queries, and a temporary table, when a single query something like this would do:

/* --optional, if you want to store the data:

if NOT exists (select * from dbo.sysobjects

               where id = object_id(N'[dbo].[SpaceUsedByObject]')

               and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Begin

CREATE Table SpaceUsedByObject

(

  [Id] INT Identity(1,1),

  ObjName sysname,

  TotalSpaceUsed INT,

  DataSpaceUsed INT,

  IndexSpaceUsed INT,

  RowCnt INT,

  TodayDate DateTime Default Getdate()

)

End

INSERT INTO SpaceUsedByObject(ObjName, TotalSpaceUsed, DataSpaceUsed, IndexSpaceUsed, RowCnt)

*/

SELECT Object_Name(sysindexes.ID) AS ObjName,

 Sum(Used) AS TotalSpaceUsed,

 Sum(CASE WHEN IndID = 255 THEN Used ELSE Dpages END) AS DataSpaceUsed,

 Sum(Used) - Sum(CASE WHEN IndID = 255 THEN Used ELSE Dpages END) AS IndexSpaceUsed,

 Sum(CASE WHEN IndID = 255 THEN 0 ELSE rowcnt END) AS RowCnt

FROM sysindexes

INNER JOIN sysobjects ON sysindexes.Id=sysobjects.Id and type='u'

WHERE Indid IN (0,1,255)

GROUP BY sysindexes.ID

ORDER BY TotalSpaceUsed DESC --Added because that was really what I was interested in

Is there any disadvantage to doing everything in a single query, eg are the CASE statements more expensive than the multiple joins, subqueries and updates?

Thanks,

Tao