Sorry,

select case when indexes.type_desc in ('HEAP','CLUSTERED')

then 'Table-' + indexes.type_desc

else ' NC Index' end as indexType,

rtrim(cast(

case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1

then 'unique ' else '' end +

case when isNull(objectProperty(object_id(schemas.name + '.'

+ indexes.name),'IsConstraint'),0) = 1

then 'constraint ' else '' end +

case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1

then 'auto ' else '' end +

case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1

then 'statistics ' else '' end +

case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1

then 'hypothetical ' else '' end

as varchar(30))) as indexProperties,

schemas.name + '.' + objects.name as tableName,

coalesce(indexes.name,'') as indexName,

filegroups.name as filegroup

from sys.indexes as indexes

join sys.objects

on indexes.object_id = objects.object_id

join sys.schemas

on objects.schema_id = schemas.schema_id

join sys.filegroups as filegroups

on indexes.data_space_id = filegroups.data_space_id

where objectproperty(indexes.object_id,'IsMSShipped') = 0

order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end