Here is a query that returns information about indexes. I use this query to get information about index from client sites, so it produces tabular format. The scripting part is left as an exercise to the reader. You should rip out the part that references sys.dm_db_index_usage_stats, as that does not affect the definition of the index.
SELECT dbname = db_name(), s.name, o.name, o.type, i.name, i.index_id,
autype = p.type_desc, ixtype = i.type_desc, PK = i.is_primary_key,
U = i.is_unique_constraint, UIX = i.is_unique,
Filter = i.filter_definition,
indexcols = left(ic.indexcols, len(ic.indexcols) - 1) +
CASE WHEN incl.includedcols IS NOT NULL
THEN ' INCL ' +
left(incl.includedcols, len(incl.includedcols) - 1)
ELSE ''
END,
p.rows,
CASE WHEN i.index_id IN (0,1) AND p.type_desc = 'IN_ROW_DATA'
THEN SUM(p.total_pages) OVER (PARTITION BY o.object_id)
END * 8192 / 1000000 AS table_size,
p.total_pages * 8192 / 1000000 AS reserved,
p.used_pages * 8192 / 1000000 AS reserved_in_use, p.no_of_parts,
ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.indexes i
LEFT JOIN (SELECT p.object_id, p.index_id, au.type_desc,
SUM(p.rows) AS rows, SUM(au.total_pages) AS total_pages,
SUM(au.used_pages) AS used_pages, COUNT(*) AS no_of_parts
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
GROUP BY p.object_id, p.index_id, au.type_desc) AS p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id()
AND ius.object_id = i.object_id
AND ius.index_id = i.index_id
OUTER APPLY (SELECT c.name + ', ' AS [text()]
FROM sys.index_columns ic
JOIN sys.columns c ON ic.column_id = c.column_id
AND ic.object_id = c.object_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')) AS ic(indexcols)
OUTER APPLY (SELECT c.name + ', ' AS [text()]
FROM sys.index_columns ic
JOIN sys.columns c ON ic.column_id = c.column_id
AND ic.object_id = c.object_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY c.name
FOR XML PATH('')) AS incl(includedcols)
WHERE o.type NOT IN ('IT', 'S', 'TF')