We all know that indexes on tables help performance. However, sometimes too much indexing could affect performance as well. One way to see some details of indexes for a table is by querying the sys.dm_db_partition_stats and the sys.indexes table. As you can see by the below, I have used the OBJECT_NAME function (passing in the Object_id) to retrieve details about a specified object. In this case, 'DimInvoiceDetail'. Go ahead and test it out, but be sure to change the 'DimInvoiceDetail' out with one of your tables that you want to see the indexes on.
Script 1: A Quick Script
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
OBJECT_NAME(p.object_id) = 'DimInvoiceDetail'
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting