Index size.

26 Nov 2007 7:18 AM

Shiju Samuel

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

26 Nov 2007 8:28 AM

Uri Dimant

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;

Show quote

"Shiju Samuel" <shiju.sam***@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

27 Nov 2007 6:45 AM

Shiju Samuel

Thanks Uri.