How To join constraint type to columns info

Question

In SQL Server, for a given table, how can I select the columns from INFORMATION_SCHEMA.COLUMNS + column with constrain type of that column, if exists. An example:

+-------------+-------------+-----------+--------------------------+-----------------+
| COLUMN_NAME | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CONSTRAINT_TYPE |
+-------------+-------------+-----------+--------------------------+-----------------+
| PersonID    | NO          | int       | NULL                     | PRIMARY KEY     |
| Name        | NO          | varchar   | 50                       |                 |
| Email       | NO          | varchar   | 50                       | UNIQUE          |
| Blog        | YES         | varchar   | 50                       |                 |
+-------------+-------------+-----------+--------------------------+-----------------+

I think I have to join INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE just cant figure how.

I tried with

SELECT COL.COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, C.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.COLUMNS COL
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
ON COL.TABLE_NAME = C.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON COL.TABLE_NAME = K.TABLE_NAME AND COL.COLUMN_NAME = K.COLUMN_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE  COL.TABLE_NAME = 'Person';

but it is duplicating the COLUMN_NAME for each CONSTRAINT_TYPE in the table, like:

+-------------+-------------+-----------+--------------------------+-----------------+
| COLUMN_NAME | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CONSTRAINT_TYPE |
+-------------+-------------+-----------+--------------------------+-----------------+
| PersonID    | NO          | int       | NULL                     | PRIMARY KEY     |
| PersonID    | NO          | int       | NULL                     | UNIQUE          |
| Name        | NO          | varchar   | 50                       | PRIMARY KEY     |
| Name        | NO          | varchar   | 50                       | UNIQUE          |
| Email       | NO          | varchar   | 50                       | PRIMARY KEY     |
| Email       | NO          | varchar   | 50                       | UNIQUE          |
| Blog        | YES         | varchar   | 50                       | PRIMARY KEY     |
| Blog        | YES         | varchar   | 50                       | UNIQUE          |
+-------------+-------------+-----------+--------------------------+-----------------+

Answer 1

You can make both queries separately in a CTE and join them.

;WITH CTE AS(
    SELECT COL.COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS AS COL
    WHERE COL.TABLE_NAME = 'Person'
)
,CTE2 AS(
    SELECT CC.COLUMN_NAME, C.CONSTRAINT_TYPE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
    where C.TABLE_NAME = 'Person'
)
SELECT CTE.*, CTE2.CONSTRAINT_TYPE
FROM CTE
LEFT JOIN CTE2 ON CTE2.COLUMN_NAME = CTE.COLUMN_NAME