Get the unique constraint columns list (in TSQL)?

sql-server tsql metadata constraints
anakic picture anakic · Apr 20, 2010 · Viewed 54.4k times · Source

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

Andrew picture Andrew · Apr 20, 2010

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

Related questions

How do you determine what SQL Tables have an identity column programmatically

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Results would be something like: TableName, ColumnName

Read More
How do you list all the indexed views in SQL Server?

How can you get a list of the views in a SQL server database that have indexes (i.e. indexed views)? I've found it's pretty easy to run an "ALTER VIEW" as I'm developing and overlook that I'm not only …

Read More