After conversing with SanDroid, I have amended this query slightly so that the schema name and indid is displayed as well. Not all of you may need to see all of this detail. If so, just filter it out. Notice the sysusers join doesn't always match up sometimes.:-D

SELECT

sys.schemas.name as [Schema Name],

sysusers.name as TABLE_USER,

sysobjects.name as TABLE_NAME,

sysindexes.rowcnt as ROWS,

sysindexes.indid as [Index],

sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),reserved*8)else 0 end) as RESERVED_Kb,

sum(case when sysindexes.indid < 2 THEN convert(dec(15),dpages*8) ELSE 0 END) +

sum(case when sysindexes.indid = 255 THEN convert(dec(15),used*8) ELSE 0 END) AS DATA_Kb,

(sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),used*8) ELSE 0 END) -

sum(case when sysindexes.indid < 2 THEN convert(dec(15),dpages*8) ELSE 0 END) -

sum(case when sysindexes.indid = 255 THEN convert(dec(15),used*8) ELSE 0 END)) AS INDEX_SIZE_Kb,

sum(case when sysindexes.indid in (0, 1, 255) then convert(dec(15),(reserved - used)*8) else 0 end) AS UNUSED_Kb

FROM sysobjects (NOLOCK)

INNER JOIN sysindexes (NOLOCK) ON sysobjects.id = sysindexes.id

LEFT JOIN sysusers (NOLOCK) ON sysobjects.uid = sysusers.uid

LEFT JOIN sys.schemas (NOLOCK) ON sysobjects.UID = sys.schemas.schema_id

WHERE (sysobjects.type = 'U')

AND (sysindexes.indid = 1)

group by sys.schemas.name,sysusers.name, sysobjects.name,sysindexes.rowcnt,sysindexes.indid

ORDER BY ROWS DESC, [SCHEMA NAME],TABLE_NAME

GO