In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a CHECK constraint.

This function accepts two parameters: the object ID, and the property for which you’re checking it for.

Therefore, you can pass the object ID as the first argument, and IsCheckCnst as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a CHECK constraint.

A return value of 1 means that it is a CHECK constraint, and a value of 0 means that it’s not.

Example 1 – Checking a CHECK Constraint

Here’s a quick example to demonstrate.

USE Test;
SELECT OBJECTPROPERTY(178099675, 'IsCheckCnst') AS [IsCheckCnst];

Result:

+---------------+
| IsCheckCnst   |
|---------------|
| 1             |
+---------------+

In this case, the Test database has an object with the ID provided, and it is in fact a CHECK constraint.

Example 2 – Getting the Object ID

If you know the object’s name, but not its ID, you can use the OBJECT_ID() function to retrieve the ID based on its name.

Example:

SELECT OBJECTPROPERTY(OBJECT_ID('chkEndDate'), 'IsCheckCnst') AS [IsCheckCnst];

Result:

+---------------+
| IsCheckCnst   |
|---------------|
| 1             |
+---------------+

In this case I checked the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('chkEndDate') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('chkEndDate'), 'IsCheckCnst') AS [IsCheckCnst];

Result:

+-------------+---------------+
| Object ID   | IsCheckCnst   |
|-------------+---------------|
| 178099675   | 1             |
+-------------+---------------+

Example 3 – Checking a Table (i.e. NOT a CHECK Constraint)

Here’s what happens when the object isn’t a CHECK constraint.

SELECT OBJECTPROPERTY(18099105, 'IsCheckCnst') AS [IsCheckCnst];

Result:

+---------------+
| IsCheckCnst   |
|---------------|
| 0             |
+---------------+

In this case, the database does in fact have an object with that ID, but the object is actually a table, so I get a negative result.

Here it is again using OBJECT_ID().

SELECT 
  OBJECT_ID('Individual') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Individual'), 'IsCheckCnst') AS [IsCheckCnst];

Result:

+-------------+---------------+
| Object ID   | IsCheckCnst   |
|-------------+---------------|
| 18099105    | 0             |
+-------------+---------------+

Example 4 – Object Doesn’t Exist

SQL Server assumes that the object ID is in the current database context. If you pass in an object ID from a different database, you’ll either get a NULL result or you’ll get incorrect results.

SELECT 
  OBJECTPROPERTY(OBJECT_ID('NonExistentObject'), 'IsCheckCnst') AS [NonExistentObject],
  OBJECTPROPERTY(11111111, 'IsCheckCnst') AS [11111111];

Result:

+---------------------+------------+
| NonExistentObject   | 11111111   |
|---------------------+------------|
| NULL                | NULL       |
+---------------------+------------+

In this case the database contains no objects of that name or ID.