获取检查约束的列

Question

我使用以下脚本获取检查约束及其相关列

select 
col.[name] as column_name,
con.[definition],    
con.[name] as constraint_name
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

但是当检查表达式涉及 2 个或更多不同的列时，例如在具有 2 个以上列的表中，并且检查约束看起来像这样([col1] <> [col2])或([col1] < 0 and [col2] = 0)时，我会收到"NULL"。

我想用字符串替换上面脚本中返回"Null"，例如"col1, col2"

Answer 1

我们可以通过返回约束级别而不是约束来改进查询。当然，这将仅返回列级约束的列名。对于表级约束NULL将返回。

column_name- 列级检查约束的列名称，表级检查约束的 null

SELECT 
cc.name AS 'Constraint',
cc.is_disabled AS 'Disabled?',
CASE WHEN cc.parent_column_id = 0 THEN 'Table-level' ELSE 'Column-level' END AS 'Table/Column',
o.name AS 'Table',
ISNULL(ac.name, '(n/a)') AS 'Column',
cc.Definition AS 'Constraint Definition'
FROM sys.check_constraints cc
LEFT OUTER JOIN sys.objects o ON cc.parent_object_id = o.object_id
LEFT OUTER JOIN sys.all_columns ac ON cc.parent_column_id = ac.column_id AND cc.parent_object_id = ac.object_id;
+-----------------+-------------+----------------+----------------+----------+----------------------------------------+
| Constraint      | Disabled?   | Table/Column   | Table          | Column   | Constraint Definition                  |
|-----------------+-------------+----------------+----------------+----------+----------------------------------------|
| chkPrice        | 0           | Column-level   | ConstraintTest | Price    | ([Price]>(0))                          |
| chkValidEndDate | 0           | Table-level    | ConstraintTest | (n/a)    | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0           | Column-level   | ConstraintTest | TeamSize | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 0           | Column-level   | Occupation     | JobTitle | ([JobTitle]<>'Digital Nomad')          |
+-----------------+-------------+----------------+----------------+----------+----------------------------------------+