USE adventureWorks

SELECT ic.index_id 
    ,ic.key_ordinal
	,i.is_primary_key
	,i.is_unique
	,ic.is_descending_key
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type 
	,i.name AS index_name
    ,ic.index_column_id
    ,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
--WHERE i.is_primary_key = 1 
--    AND i.object_id = OBJECT_ID('HumanResources.EmployeeDepartmentHistory');
WHERE i.object_id = OBJECT_ID('HumanResources.EmployeeDepartmentHistory')
order by ic.index_id, ic.key_ordinal