Query to display foreign key relationships in SQL Server

Below is a query to display foreign key relationships in a database.

SELECT fks.NAME                                                       AS 
       ForeignKey, 
       fks.is_disabled,
	   Schema_name(fks.schema_id) + '.' 
       + Object_name(fks.parent_object_id)                            AS 
       TableName, 
       Col_name(fkcs.parent_object_id, fkcs.parent_column_id)         AS 
       ColumnName, 
       (SELECT Schema_name(schema_id) + '.' + NAME 
        FROM   sys.objects 
        WHERE  object_id = fks.referenced_object_id)                  AS 
       --Object_name (fks.referenced_object_id)                       AS  
       ReferenceTableName, 
       Col_name(fkcs.referenced_object_id, fkcs.referenced_column_id) AS 
       ReferenceColumnName, 
       fks.delete_referential_action_desc, 
       fks.update_referential_action_desc 
FROM   sys.foreign_keys AS fks 
       INNER JOIN sys.foreign_key_columns AS fkcs 
               ON fks.object_id = fkcs.constraint_object_id

The delete_referential_action_desc and update_referential_action_desc column describes the action that occurs when a delete or update occurs. The values available here are no action, cascade, set null and set default. A foreign key is active when is_disabled is 0.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook