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

query to display foreign key relationships in sql server

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *