Home > Catalog Views, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Catalog Views – sys.check_constraints

Using Catalog Views – sys.check_constraints


sys.check_constraints contains a row for each CHECK constraint. It contains below useful columns:

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 – definition of the constraint

for other columns returned by this view refer BOL.

 

for example, let’s create a new CHECK constraint as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE TABLE dbo.Salary

(

      ID          INT   NOT NULL,

      NAME        VARCHAR(50),

      SALARY      MONEY NOT NULL

)

 

ALTER TABLE dbo.Salary

ADD CONSTRAINT CHK_SALARY CHECK (SALARY BETWEEN 1000 AND 5000)

Result Set:

Command(s) completed successfully.

 

The CHECK constraint and it’s definition can be viewed in sys.check_constraints:

SELECT      Name, Parent_Object_ID, Create_Date, Modify_Date, Definition

FROM        sys.check_constraints

WHERE       Parent_Object_ID = OBJECT_ID('dbo.Salary')

Result Set:

image

If you need to identify the column name on which the CONSTRAINT is defined, you can JOIN sys.check_constraints with sys.columns as below:

SELECT      B.Name, A.Name, A.Create_Date, A.Modify_Date, A.Definition

FROM        sys.check_constraints A

INNER JOIN  sys.columns B ON B.object_id = A.parent_object_id

WHERE       parent_object_id = OBJECT_ID('dbo.Salary')

AND         B.column_id = A.parent_column_id

Result Set:

image

 

Hope This Helps!

Vishal

  1. Petr Kodytek
    14.11.2012 05:12 | #1

    Hello,

    and what about table level check constraint like :

    ALTER TABLE dbo.Salary
    ADD CONSTRAINT CHK_SALARY CHECK (SALARY1 < SALARY2)

    In this case is parent_column_id = 0

    I've found only way in [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] which contains list of used columns.

    Have a nice day.
    Petr

  1. No trackbacks yet.