[font="Verdana"]The subqueries where a bit complicated for my liking, so I took your code and pulled out the subqueries into CTEs. Also, I have had indexes turn up with -ve numbers, so I don't like relying on the index_id being < 2 (I use an explicit (0, 1). That may be a hang over from statistics in SQL Server 2000, but I figure it's better safe than sorry.

Runs a wee bit quicker this way in my testing. Anyway, the ideas are still yours. Here's the code variation.

with

spart as (

select object_id,

sum([rows]) as [RowCount]

from sys.partitions

where index_id in (0, 1)

group by

object_id

),

sz as (

select i.object_id,

cast(round(

cast(v.low as decimal(36,4)) *

sum(

case when a.type <> 1 then 0

when p.index_id in (0, 1) then a.used_pages - a.data_pages

else a.used_pages

end)

/ 1024.00,

0)

as int) as [IndexKB],

cast(round(

cast(v.low as decimal(36,4)) *

sum(

case when a.type <> 1 then a.used_pages

when p.index_id in (0, 1) then a.data_pages

else 0

end)

/ 1024.00,

0)

as int) as [DataKB]

from sys.indexes as i

inner join

sys.partitions as p

on p.object_id = i.object_id and

p.index_id = i.index_id

inner join

sys.allocation_units as a

on a.container_id = p.partition_id

inner join

master.dbo.spt_values v

on v.number = 1 and

v.type = 'E'

group by

v.low,

i.object_id

)

select schema_name(tbl.schema_id) as [Schema],

tbl.Name as [Table],

isnull(pr.name, schema_name(tbl.schema_id)) as [Owner],

tbl.max_column_id_used as [Columns],

cast(idx.index_id as bit) as [ClusteredIndex],

isnull(spart.[RowCount], 0) as [RowCount],

isnull(sz.[IndexKB], 0) as [IndexKB],

isnull(sz.[DataKB], 0) as [DataKB],

tbl.create_date,

tbl.modify_date

from sys.tables as tbl

inner join

sys.indexes as idx

on idx.object_id = tbl.object_id and

idx.index_id in (0, 1)

left join

sys.database_principals pr

on pr.principal_id = tbl.principal_id

left join

spart

on tbl.object_id = spart.object_id

left join

sz

on tbl.object_id = sz.object_id;

[/font]