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.