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