How to bind constraint type to column data
In SQL Server for a given table, how can I select columns from a column INFORMATION_SCHEMA.COLUMNS
+ from constrain type
that column, if exists. 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 need to join the INFORMATION_SCHEMA.COLUMNS
, INFORMATION_SCHEMA.TABLE_CONSTRAINTS
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE
I just can not figure out how to do it.
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 duplicates COLUMN_NAME for every 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 |
+-------------+-------------+-----------+--------------------------+-----------------+
+3
source to share
1 answer
You can make both requests separately in 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
+2
source to share