Get the unique constraint columns list (in TSQL)?

Question

I can get a list of unique constraints fairly easily with the following query:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE'

But how do I get a list of the columns that each unique constraint applies to?

Answer 1

See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Answer 2

Ed is correct, the columns are exposed on the constraint column usage view, to make your life easier, the krufted sql for it.

select TC.Constraint_Name, CC.Column_Name from information_schema.table_constraints TC
inner join information_schema.constraint_column_usage CC on TC.Constraint_Name = CC.Constraint_Name
where TC.constraint_type = 'Unique'
order by TC.Constraint_Name

Answer 3

SELECT *
FROM    sys.indexes
        JOIN sys.index_columns ON indexes.index_id = index_columns.index_id
WHERE indexes.is_unique_constraint = 1;