How to list all indexes in a database?

Execute the following Microsoft SQL Server T-SQL script for listing index metadata including index columns, index names, unique and clustered properties. Ordinal = 0 means INCLUDEd non-key column.

-- SQL Server list all indexes in database - SQL Server database schema 
USE AdventureWorks2008;
SELECT   SchemaName = schema_name(schema_id), 
         TableName = object_name(t.object_id), 
         ColumnName = c.name, 
         [Unique] = CASE 
                      WHEN i.is_unique = 1 THEN 'Yes' 
                      ELSE 'No' 
                    END, 
         [Clustered] = CASE 
                         WHEN i.index_id = 1 THEN 'C' 
                         ELSE 'NC' 
                       END, 
         Ordinal = key_ordinal,
         IndexName = i.Name 
FROM     sys.indexes i 
         INNER JOIN sys.tables t 
           ON i.object_id = t.object_id 
         INNER JOIN sys.index_columns ic 
           ON ic.object_id = t.object_id 
              AND ic.index_id = i.index_id 
         INNER JOIN sys.columns c 
           ON c.object_id = t.object_id 
              AND ic.column_id = c.column_id 
ORDER BY SchemaName, 
         TableName, 
         IndexName, 
         key_ordinal;
/*
SchemaName  TableName ColumnName   Unique Clustered   Ordinal     IndexName
....
Production  Product   Color         No    NC          0     idxProdInclude
Production  Product   ListPrice     No    NC          0     idxProdInclude
Production  Produc    ProductNumber No    NC          1     idxProdInclude
Production  Product   Name          No    NC          2     idxProdInclude
....*/
------------

-- List all indexes in single row with column list
USE AdventureWorks2008; 
SELECT   DISTINCT SchemaName = schema_name(schema_id), 
         TableName = object_name(t.object_id), 
         ColumnList = Stuff((SELECT  ', ' + c1.Name AS [text()] 
                            FROM sys.indexes i1 
         INNER JOIN sys.tables t1 
           ON i1.object_id = t1.object_id 
         INNER JOIN sys.index_columns ic1 
           ON ic1.object_id = t1.object_id 
              AND ic1.index_id = i1.index_id 
         INNER JOIN sys.columns c1 
           ON c1.object_id = t1.object_id 
              AND ic1.column_id = c1.column_id 
                            WHERE t.schema_id = t1.schema_id
                                  and t.object_id = t1.object_id
                                  and i.name =i1.name
                            FOR XML PATH ('')),1,1,''), 
         IndexName = i.Name 
FROM     sys.indexes i 
         INNER JOIN sys.tables t 
           ON i.object_id = t.object_id 
         INNER JOIN sys.index_columns ic 
           ON ic.object_id = t.object_id 
              AND ic.index_id = i.index_id 
         INNER JOIN sys.columns c 
           ON c.object_id = t.object_id 
              AND ic.column_id = c.column_id 
ORDER BY SchemaName, 
         TableName, 
         IndexName
/* Partial results
SchemaName  TableName                     ColumnList
Production  ProductDocument              ProductID, DocumentNode
Production  ProductInventory             ProductID, LocationID
Production  ProductListPriceHistory      ProductID, StartDate
*/
------------