• If you are still running some SQL 2K databases, then the following will get you the space usage results from the system tables (no need for cursors or temp tables).

    Note that for a clustered index, the sp_spaceused query will show the intermediate 'node' level pages as a separate index size while the data and 'leaf level' of the index are counted as one and the same.

    So for a table of 50 pages allocated having a clustered index you might see it split as 40 pages of data and 10 for index (really only the intermediate level of the clustered index) when you do the sp_spaceused.

    You might also consider doing a "DBCC UPDATEUSAGE (0) " before running your query to get the latest data (though SQL should keep this relatively current). Alternatively with a sp_spaceused you can use the optional '@updateusage=true' parameter to the same ends.

    Toni

    SELECT so.name as TableName,

    case when si.indid=255 then 'Image_Text Info'

    when si.indid <2 then 'Total for Table'

    else 'Index_Info' end [Data Type],

    case when si.indid=255 then 'N/A'

    when si.indid>=2 then 'N/A'

    when si.indid <2 then cast(si.rowcnt as varchar(20)) end [Rows],

    si.reserved * 8 [Allocated_KB],

    si.used*8 [Used_kb],

    (si.reserved-si.used)*8 [Unused_KB]

    FROM sysobjects so

    JOIN sysindexes si

    ON si.id = so.id

    WHERE so.Type='U' AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0 and si.reserved > 0

    order by so.Name

    ** updated for spelling and to clarify 'Data Total' column as the total usage for the table. ** Toni