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
SELECT
OBJECT_NAME(p.object_id)
, i.name
, p.*
FROM
sys.dm_db_partition_stats p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE
OBJECT_NAME(p.object_id) = 'DimInvoiceDetail'
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting
Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter