Unique Index Schema Information in SQL Server

Unique indexes along foreign key constraints are used to model One-To-One relationships in Database model design. In fact, when it comes to the DB design, the only difference between a One-To-One relationship and a One-To-Many relationship is that Unique index. For Data Access code generation purposes the existence of the unique index will effectively tell you, for example if you are generating navigation properties, whether to create a simple entity type navigation (One-To-One) or an entity array type navigation (One-To-Many) between the two entities that take part in the data base relationship.

In ADO.NET the GetSchema methods can be used to get Schema information from different Data base provider types; unfortunately for SQL Server Databases unique index information is not provided with that API. Don't panic though, one way or another SQL Server hast to store the unique index information in its catalog tables so after digging into the master database views list for a while, we bump into the table sys_indexes and its is_unique column. An index can be applied upon many columns therefore we'll need to join with sys.index_columns and sys.columns in order to get the column name as well.

SELECT c.[name] as column_name, a.[name] as index_name, is_unique
FROM sys.indexes a INNER JOIN sys.index_columns b
ON a.object_id = b.object_id AND a.index_id = b.index_id
INNER JOIN sys.columns c
ON b.object_id = c.object_id and b.column_id = c.column_id
WHERE a.object_id = OBJECT_ID(N'dbo.yourTable')