Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju Samuel
Hi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel
|||Thanks Uri.