STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Guy Glantser

Enhanced Indexes List

It is often useful to query the list of all indexes in a database. Such a report can help in tuning the database or in designing a new feature.


One way to achieve this is to use the sys.sp_helpindex system stored procedure. This stored procedure displays all the indexes for a specific table or view. It displays the information about each index in a single description, which is not so convenient if you want to manipulate the data in some way. It also displays a list of the index keys, but it does not display the included columns. And if you want to generate a list of the indexes in the whole database, you’ll have to execute this stored procedure in a loop for every table or view.


The alternative is, of course, to query the system views and functions. There are several catalog views and dynamic management views that store relevant information, and you need to know the details in order to write the query correctly. Here is a query that will save you some time.


The query displays the following information for all the indexes in the current database. It displays the information for every partition of every index.


  • Schema name, object name and object ID of the relevant object

  • Object type (table or view)

  • ID and name of the index

  • Index type (clustered or non-clustered)

  • Constraint type (primary key, unique constraint, unique index or none)

  • Filter definition, in case it's a filtered index

  • List of index keys

  • List of included columns, if any

  • Partition number

  • Number of rows and number of pages

  • Index fragmentation level

  • Index fill factor

  • Usage level (low, high or none) - Consider dropping or altering indexes with a low or none usage


You can find the query in our Toolbox in GitHub.

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle