SELECT TOP 50
'[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName
,Ind.type_desc AS IndexType
,Ind.[name] AS IndexName
,SUBSTRING((SELECT ', ' + AC.[name] FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 0
ORDER BY IC.key_ordinal FOR XML PATH('')), 2, 8000) AS KeyColumns
,SUBSTRING((SELECT ', ' + AC.[name] FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 1
ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS IncludeColumns
,usg_stats.user_seeks AS UserSeek
,usg_stats.user_scans AS UserScans
,usg_stats.user_lookups AS UserLookups
,usg_stats.user_updates AS UserUpdates
,o.create_date
,usg_stats.last_user_scan
,usg_stats.last_system_seek
FROM sys.[indexes] AS Ind
INNER JOIN sys.objects o on o.[object_id] = Ind.[object_id]
INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
LEFT JOIN sys.dm_db_index_usage_stats AS usg_stats ON Ind.index_id = usg_stats.index_id
AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID] and usg_stats.database_id = DB_ID()
WHERE Ind.type_desc <> 'HEAP' --AND Tab.name = 'YourTableName' -- uncomment to get single table indexes detail
ORDER BY o.create_date desc