SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get Index Details

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



      , i.name

      , p.*


      sys.dm_db_partition_stats p

      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

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter



No comments.

Leave a Comment

Please register or log in to leave a comment.