How to get list of columns with Unique Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 98

Scenario: 

You are working as SQL Server developer, you are asked to provide a query that should return all the Unique Constraint with Columns, Table and Schema Name.

Solution:

We can use system objects to get list of unique constraints with columns, tables and schema name. Below query will return you all the unique constraints with columns from SQL Server database.

;WITH CTE_UQ
AS (
    SELECT t.Table_Schema AS TableSchema
        ,t.table_name AS TableName
        ,c.column_name AS ColumnName
        ,t.constraint_name AS UniqueConstraintName
        ,t.constraint_type AS ConstraintType
    FROM information_schema.table_constraints t
    LEFT JOIN information_schema.key_column_usage c 
    ON t.constraint_catalog = c.constraint_catalog
        AND t.constraint_schema = c.constraint_schema
        AND t.constraint_name = c.constraint_name
    WHERE t.constraint_type = 'UNIQUE'
    )
SELECT TableSchema
    ,TableName
    ,UniqueConstraintName
    ,ConstraintType
    ,stuff((
            SELECT ',' + ColumnName
            FROM CTE_UQ i
            WHERE i.TableSchema = o.TableSchema
                AND i.TableName = o.TableName
                AND i.UniqueConstraintName = o.UniqueConstraintName
            FOR XML path('')
            ), 1, 1, '') UniqueColumnList
FROM CTE_UQ o
GROUP BY TableSchema
    ,TableName
    ,UniqueConstraintName
    ,ConstraintType

I executed above query on database and got below results.

How to get Column List with Unique Constraints in SQL Server Database

Video Demo : How to get all unique Constraints in SQL Server Database with Column Names