sql - 如何将约束类型加入列信息

在 SQL Server 中，对于给定的表，如果存在，如何从 INFORMATION_SCHEMA.COLUMNS + 列中选择该列的 constrain type 列。
一个例子:

+-------------+-------------+-----------+--------------------------+-----------------+
| 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                       |                 |
+-------------+-------------+-----------+--------------------------+-----------------+

我想我必须加入 INFORMATION_SCHEMA.COLUMNS 、 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 、 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 只是不知道如何。

我试过

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';

但它为表中的每个 CONSTRAINT_TYPE 复制 COLUMN_NAME，例如:

+-------------+-------------+-----------+--------------------------+-----------------+
| 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          |
+-------------+-------------+-----------+--------------------------+-----------------+

最佳答案

您可以在 CTE 中分别进行两个查询并加入它们。

;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

关于sql - 如何将约束类型加入列信息，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30157630/