Check Index Size

use myDB

select	i.name									as index_name, 
		i.type_desc, 
		i.is_unique, 
		ds.type_desc							as filegroup_or_partition_scheme, 
		ds.name									as filegroup_or_partition_scheme_name,
		(sz.used_page_count*8.0)/(1024*1024)	as index_size_gb, 
		i.ignore_dup_key, 
		i.is_primary_key, 
		i.is_unique_constraint, 
		i.fill_factor, 
		i.is_padded, 
		i.is_disabled, 
		i.allow_row_locks, 
		i.allow_page_locks, 
		i.has_filter, 
		i.filter_definition
  from	sys.indexes								as i
		inner join sys.data_spaces				as ds on i.data_space_id	= ds.data_space_id
		inner join sys.dm_db_partition_stats	as sz on i.object_id		= sz.object_id 
													 and i.index_id			= sz.index_id
		inner join sys.tables					as t  on i.object_id		= t.object_id
  where is_hypothetical	=	0 
  and	i.index_id		<>	0
  order by 6 desc