Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

INFORMATION_SCHEMA.CHECK_CONSTRAINTS



By:

Overview
The INFORMATION_SCHEMA.CHECK_CONSTRAINTS view allows you to get information about the check constraints that are setup in your database.  A check constraint is a constraint put on a particular column in a table to ensure that specific data rules are followed for a column.

Explanation
This view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name Data type Description
CONSTRAINT_CATALOG nvarchar(128) Constraint qualifier.
CONSTRAINT_SCHEMA nvarchar(128) Name of the schema to which the constraint belongs.
CONSTRAINT_NAME sysname Constraint name.
CHECK_CLAUSE nvarchar(4000) Actual text of the Transact-SQL definition statement.

(Source: SQL Server 2005 Books Online)


Here is an example of data that was pulled from the AdventureWorks database.  This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

To take a look at this a little further here is a closer look at the first two check constraints.  These are usually setup when a table is created.

-- this says that the rating value has to be between the values of 1 and 5
([Rating]>=(1) AND [Rating]<=(5))

-- this says that the TransactionType has to be an upper case value of P, S or W
(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

 

The output from this schema view only shows the constraint name and the constraint details.  It does not show the table the constraint is used for.  To get this information you can use the INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE view.


Last Update: 7/2/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools