Archive

Posts Tagged ‘sys.key_constraints’

Using Catalog Views – sys.key_constraints and sys.default_constraints

June 17, 2011 Leave a comment

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 (http://SqlAndMe.com)

 

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:

image

 

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,

3. Type – ‘D’ = DEFAULT CONSTRAINT

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

— © 2011 – Vishal (http://SqlAndMe.com)

 

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:

image

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe