Querying SQL Constraints

One of the important data while dealing with Meta data is constraints.

Obvious way (for most of the programmers) to look at Constraints (whether it is Check Constraint or Key Constraint or Default Constraints) is through designer and visualizing it.

But below is another way to take a look at the same using SQL queries. Consider the below database with two tables where there is a Foreign key relationship between department and employee tables.

Looking at the designer one would easily say that this table has primary key, a foreign key and some constraints and so on. But what if, there is a need to determine these through the query (in other words, while run time?).

As you can see that there is constraints defined for the column, external_id, executing below query will give you the name of the default constraints (and details if queried) for that column.

SELECT
DCS.name AS CONSTRAINT_NAME,
OBJ.name AS TABLE_NAME,
COLS.name AS COLUMN_NAME,
OBJECT_DEFINITION(COLS.default_object_id) AS DEFAULT_DEFINTION
FROM sys.default_constraints DCS
JOIN sys.objects OBJ
ON OBJ.object_id = DCS.parent_object_id
JOIN sys.columns COLS
ON OBJ.object_id = COLS.object_id
AND
COLS.column_id = DCS.parent_column_id
WHERE
OBJ.name = 'Employee'
AND
COLS.name ='external_id'

Explanation

Joining the sys.default_constraints table with sys.objects and sys.columns table will give us all necessary sets require to pull the information required. As constraint is defined on the column, it is linked to that column with parent column id (where parent column id is actually a column id pointing back to sys.columns table) and we have used the same column in above query to get the specific column data.

The output of the above query looks like this:

Note: when you want to explore the definition of default values for specific column, use OBJECT_DEFINTION function that takes the object id as input parameter and returns the varchar string containing the default value of that column.

Similarly, if you want to query the Check constraints, you can use the below query.

SELECT
CC.name AS CONSTRAINT_NAME,
OBJ.name AS TABLE_NAME,
COLS.name AS COLUMN_NAME,
CC.definition AS CONSTRAINT_DEFINTION
FROM sys.check_constraints CC
JOIN sys.objects OBJ
ON OBJ.object_id = CC.parent_object_id
JOIN sys.columns COLS
ON OBJ.object_id = COLS.object_id
AND
COLS.column_id = CC.parent_column_id
WHERE
OBJ.name = 'Employee'

Output

Below is the query for checking the KEY constraints for both the tables.

SELECT
KC.parent_object_id,
KC.name AS CONSTRAINT_NAME,
OBJ.name AS TABLE_NAME,
COLS.name AS COLUMN_NAME,
KC.type_desc AS KEY_TYPE
FROM sys.key_constraints KC
JOIN sys.objects OBJ
ON OBJ.object_id = KC.parent_object_id
JOIN sys.columns COLS
ON OBJ.object_id = COLS.object_id

Output

Please leave your questions or doubts in comments, I would be happy to help.

5 Comments

Join the conversation! Your thoughts help the community grow.

Tapan Patel10y ago
Http://www.c-sharpcorner.com/blogs/querying-sql-objects
Tapan Patel10y ago
Thanks for the comment. Here is one more (probably you already visited) http://www.c-sharpcorner.com/blogs/querying-sql-objects.
- Subash10y ago
  Thanks but when i copy this link page not found if you don't mind can i get correct rurl please
Subash10y ago
I think here you only say about primary key constraint.we wait for others too
Subash10y ago
Good