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.

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:


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 (*).

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.


get free sql tips
agree to terms

Learn more about SQL Server tools