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'