Script : index Information by Using DMV and INDEXPROPERTY Option

you can use this DMV Script to Show Index information in your database

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
—and obj.name = ‘Computed_Cal’ —where you need to Show index info in Specfice table
–And ind.type_desc = ‘Heap’ — Where you need the Tables not have any Indexes