Microsoft SQL Server Index investigations


You could use "SP_HELPINDEX Table_Name", but Kimberly Tripp has a stored procedure (that can be found here), which is better example, as it shows more about the indexes, including columns and filter definition, for example:

USE Adventureworks 
EXEC sp_SQLskills_SQL2012_helpindex 'dbo.Product'

Alternatively, Tibor Karaszi has a stored procedure (found here). The later will show information on index usage too, and optionally provide a list of index suggestions. Usage:

USE Adventureworks 
EXEC sp_indexinfo 'dbo.Product'