Get the unique constraint columns list (in TSQL)?

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?


Ed is correct, the columns are exposed on the constraint column usage view, here is the 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

See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE


SELECT *
FROM    sys.indexes i
        JOIN sys.index_columns ic
            ON i.index_id = ic.index_id
               AND i.object_id = ic.object_id
WHERE i.is_unique_constraint = 1;

Just for reference of mySQL users, Same thing can be achieved with below queries:

To find any unique constraint on a table

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE' and table_name='db_my_table'

To find unique column list with all column

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME='cons_name' and TABLE_NAME='db_my_table'

to find unique column list with required view

select CONSTRAINT_NAME,COLUMN_NAME,TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME='cons_name' and TABLE_NAME='db_my_table'