Archive

Posts Tagged ‘FK’

Using Catalog Views – sys.foreign_keys

14.06.2011 1 comment

sys.foreign_keys contains a list of all FOREIGN KEYS in the database. It contains one row per FOREIGN KEY. Below is a list of some columns returned by this view:

1. Name – FOREIGN KEY name,

2. Schema_ID – Schema ID of the parent object,

3. Parent_Object_ID – ID of the object to which the FK is linked.

4. Type – 'F' = FOREIGN KEY,

5. Create Date – Date constraint was created,

6. Modify_Date – Last Modified date for constraint,

7. Referenced_Object_ID – well, referenced object’s ID,

8. Delete_Referential_Action – ON DELETE action, this contains one of the following: 0 = No action, 1 = Cascade, 2 = Set null, 3 = Set default,

9. Update_Referential_Action – ON DELETE action, this contains one of the following: 0 = No action, 1 = Cascade, 2 = Set null, 3 = Set default,

10. Delete/Update_Referential_Action_Desc – Textual description of the referential action

 

sys.foreign_keys can be used as below:

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

 

USE AdventureWorks2008R2

 

SELECT      Name, Create_Date, Modify_Date,

            OBJECT_NAME(referenced_object_id) AS 'referenced object',

            delete_referential_action_desc,

            update_referential_action_desc

FROM        sys.foreign_keys

WHERE       parent_object_id = OBJECT_ID(N'HumanResources.Employee')

Result Set:

image

Hope This Helps!

Vishal