List all constraints of a particular table - SQL Server

If you want to have a list of constraints applied on a particular table in the SQL server, this will help you to get it in one go.

DECLARE @TABLENAME VARCHAR(50) = '<table_name>'

SELECT ObjectName
    ,TypeOfObject
    ,TypeOfConstraint
    ,ConstraintName
    ,ConstraintDescription
FROM (
    SELECT schema_name(t.schema_id) + '.' + t.[name] AS ObjectName
        ,CASE 
            WHEN t.[type] = 'U'
                THEN 'Table'
            WHEN t.[type] = 'V'
                THEN 'View'
            END AS [TypeOfObject]
        ,CASE 
            WHEN c.[type] = 'PK'
                THEN 'Primary key'
            WHEN c.[type] = 'UQ'
                THEN 'Unique constraint'
            WHEN i.[type] = 1
                THEN 'Unique clustered index'
            WHEN i.type = 2
                THEN 'Unique index'
            END AS TypeOfConstraint
        ,ISNULL(c.[name], i.[name]) AS ConstraintName
        ,SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [ConstraintDescription]
    FROM sys.objects t
    LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id
        AND i.index_id = c.unique_index_id
    CROSS APPLY (
        SELECT col.[name] + ', '
        FROM sys.index_columns ic
        INNER JOIN sys.columns col ON ic.object_id = col.object_id
            AND ic.column_id = col.column_id
        WHERE ic.object_id = t.object_id
            AND ic.index_id = i.index_id
        ORDER BY col.column_id
        FOR XML path('')
        ) D(column_names)
    WHERE is_unique = 1
        AND t.name = @TABLENAME
        AND t.is_ms_shipped <> 1
    
    UNION ALL
    
    SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name AS foreign_table
        ,'Table'
        ,'Foreign key'
        ,fk.name AS fk_ConstraintName
        ,cols.[name] + ' REFERENCES ' + schema_name(pk_tab.schema_id) + '.' + pk_tab.name + ' (' + c2.[name] + ')'
    FROM sys.foreign_keys fk
    INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
    INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
    INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
    INNER JOIN sys.columns cols ON cols.object_id = fk_cols.parent_object_id AND cols.column_id = fk_cols.parent_column_id
    INNER JOIN sys.columns c2 ON c2.object_id = fk_cols.referenced_object_id AND c2.column_id = fk_cols.referenced_column_id
    WHERE fk_tab.name = @TABLENAME
        OR pk_tab.name = @TABLENAME
    
    UNION ALL
    
    SELECT schema_name(t.schema_id) + '.' + t.[name]
        ,'Table'
        ,'Check constraint'
        ,con.[name] AS ConstraintName
        ,con.[definition]
    FROM sys.check_constraints con
    LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
    LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
        AND con.parent_object_id = col.object_id
    WHERE t.name = @TABLENAME
    
    UNION ALL
    
    SELECT schema_name(t.schema_id) + '.' + t.[name]
        ,'Table'
        ,'Default constraint'
        ,con.[name]
        ,col.[name] + ' = ' + con.[definition]
    FROM sys.default_constraints con
    LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
    LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
        AND con.parent_object_id = col.object_id
    WHERE t.name = @TABLENAME
    ) a
ORDER BY ObjectName
    ,TypeOfConstraint
    ,ConstraintName

Output:

Enjoy.!