What is the use of sys.indexes?

Answer:

The sys.indexes catalog view provides information on each index in a database. For every table, index, or table-valued function there is one row within the catalog view. This provides a full accounting of all indexes in a database.

The information in sys.indexes is useful in a few ways.

First, the catalog view includes the name of the index. Along with that is the type of the index, identifying whether the index is clustered, nonclustered, and so forth.

Along with that information are the properties on the definition of the index. This includes the fill factor, filter definition, the uniqueness flag, and other items that were used to define the index.