O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT OBJECT_NAME(object_id) AS table_name,
 name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

To identify what indexes exist on an object.