sqlsql-serverinformation-schemaunique-index

Unique indexes are not necessarily in the table_constraints view: so how to find them using the information_schema?

This query will not find unique indexes not explicitly used in conjunction with a constraint:

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

So then how can an implied unique constraint such as the following be found using the INFORMATION_SCHEMA ?

create unique constraint PipelineSummary_CorrelationId on
bronze.AggregatedPipelineSummary (CorrelationId);
Solution

  • The INFORMATION_SCHEMA views provide a standardized and simplified view of your database catalog. It's limited to displaying the "logical" view of your database, and won't display many of the SQL Server-specific aspects of your database design.

    So use SQL Server's native catalog views instead, here sys.indexes, eg:

    select o.name table_name, i.name index_name, i.is_unique_constraint
from sys.objects o
join sys.indexes i
  on o.object_id = i.object_id
where is_unique=1
and o.is_ms_shipped = 0