By: Greg Robidoux
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 |
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.
