Execute the following script to all indexes in AdventureWorks database sorted by IndexType. XML indexes included, full-text indexes not included.

USE AdventureWorks2008;

SELECT

      SchemaName = schema_name(schema_id),

      TableName = object_name(o.object_id),

      IndexName = i.Name,

      IndexType = i.type_desc,

      IsUnique = case when is_unique=1 then 'UNIQUE' else '' end,

      IsPrimaryKey = case when is_primary_key=1 then 'PRIMARY KEY' else '' end,

      [FillFactor] = i.fill_factor

FROM sys.indexes i

INNER JOIN sys.objects o

ON i.object_id = o.object_id

WHERE i.Name is not null

and o.type = 'U'

ORDER BY IndexType, SchemaName, TableName, IndexName