SQL DBA - Get Details of Index names in a DB

SELECT

OBJECT_NAME(DMV.object_id) AS TABLE_NAME

,SI.NAME AS INDEX_NAME

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'Detailed') as DMV

LEFT OUTER JOIN SYS.INDEXES AS SI

ON DMV.OBJECT_ID = SI.OBJECT_ID

AND DMV.INDEX_ID = SI.INDEX_ID

WHERE index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')