Using SQL Server Dynamic Management Views (DMVs):

SELECT 
 OBJECT_NAME(i.object_id) AS TableName,
 i.name AS IndexName,
 i.index_id,
 i.type_desc AS IndexType,
 p.rows AS RowCounts,
 SUM(a.total_pages) * 8 AS TotalSpaceKB,
 MAX(ius.last_user_seek) AS LastUserSeek,
 MAX(ius.last_user_scan) AS LastUserScan,
 MAX(ius.last_user_lookup) AS LastUserLookup,
 MAX(ius.last_user_update) AS LastUserUpdate
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND 
i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = 
a.container_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = 
ius.object_id AND i.index_id = ius.index_id
WHERE i.is_primary_key = 0 AND i.is_unique = 0 AND i.type_desc = 
'NONCLUSTERED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, p.rows
HAVING MAX(ius.user_seeks) IS NULL AND MAX(ius.user_scans) IS NULL
AND MAX(ius.user_lookups) IS NULL
ORDER BY TotalSpaceKB DESC;

Using SQL Server Management Studio (SSMS):
o Open SSMS and connect to your SQL Server.
o Find the database to analyze, right-click it, and choose
Reports > Standard Reports > Index Usage Statistics.

Use the sp_helpindex stored procedure

EXEC sp_helpindex 'YourTableName';