Posts Tagged ‘sys.default_constraints’

Using Catalog Views – sys.key_constraints and sys.default_constraints

17.06.2011 No comments

While sys.check_constraints returns all CHECK constraints in the database, sys.key_constraints and sys.default_constraints returns primary/unique key constraints and DEFAULT constraints respectively.

some of the columns returned by sys.key_constraints are:

1. Name – Name of the constraint,

2. Schema_ID – Schema ID of the parent object,

3. Parent_object_id – object id of the table on which the constraint is enforced,

4. Type – PK or UQ, Primary Key or Unique Key,

5. Type_desc – Textual description of ‘Type’,

6. Create/Modify_date – Creation / Last Modification date time,

7. Unique_index_id –  ID of the index that is created to enforce the constraint

— © 2011 – Vishal (


USE AdventureWorks2008R2


SELECT name, type, create_date, modify_date,

       SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)

       AS 'Enforced On Table'

FROM   sys.key_constraints

Partial Result Set:



Same way, sys.default_constraints returns all DEFAULT constraints in the database, It returns below columns which are specific to DEFAULT constraints:

1. Parent_column_id – Column id on which the DEFAULT constraint is enforced,

2. Definition – Definition of DEFAULT constraint,


COL_NAME() can be used to retrieve column name as:

— © 2011 – Vishal (


SELECT name, type, create_date, modify_date, definition,

       SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)

       AS 'Enforced On Table',

       COL_NAME(parent_object_id, parent_column_id)

       AS 'Enforced On Column'

FROM   sys.default_constraints

Partial Result Set:


Hope This Helps!