Article

SQL Server: How to retrieve the check constraints on columns in a specific table

sys.check_constraints contains a row for each CHECK constraint

1. parent_column_id – column id for which CHECK constraint is defined

2. parent_object_id – id of the object for whose column the constraint is defined.

3. create/modify_date – creation/modification date

4. definition – constraint’s criteria

Since the name of the column the constraint is built on is not present in sys.check_constraints, you need to join sys.check_constraints with sys.columns

/* get the definition of the check constraints on a single table */

SELECT columns.name AS column_name,
 checks.name AS check_constraint_name,
 checks.create_date,
 checks.modify_date,
 checks.definition,
 checks.is_disabled
FROM sys.check_constraints AS checks
 INNER JOIN sys.columns AS columns ON columns.object_id = checks.parent_object_id
 AND checks.parent_column_id = columns.column_id
WHERE ( checks.parent_object_id = OBJECT_ID('dbo.table_name') )

/* get the definition of the check constraints on a single table */

SELECT columns.name AS column_name,

checks.name AS check_constraint_name,

checks.create_date,

checks.modify_date,

checks.definition,

checks.is_disabled

FROM sys.check_constraints AS checks

INNER JOIN sys.columns AS columns ON columns.object_id = checks.parent_object_id

AND checks.parent_column_id = columns.column_id

WHERE ( checks.parent_object_id = OBJECT_ID('dbo.table_name') )

Comments Closed.