Try this script.

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