How to identify Foreign Key dependencies in SQL Server database?

To understand the database schema design especially to determine the referential integrity constraints and dependencies, here I’m sharing a sample script which will help you in following:

1)      To identify which table(s) are dependent on a given object

2)      Which columns are being referenced (have foreign keys defined on them)

3)      What action to perform in response to update/delete operations

Note: If you would like to run the below script to identify the forign key dependencies w.r.t to only one table e.g. ‘My Table’, uncomment the filter in the WHERE clause

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',fk.referenced_object_id as ReferencedObjID,

       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS bit)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS bit)) as [ForeignSchema],
      [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(fk.referenced_object_id,'CnstIsUpdateCascade')
                           WHEN 1 THEN 'CASCADE'
                           ELSE 'NO_ACTION'
                           END),
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(fk.referenced_object_id,'CnstIsDeleteCascade')
                           WHEN 1 THEN 'CASCADE'
                           ELSE 'NO_ACTION'
                           END)

FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1
          ON fk.parent_column_id = clm1.column_id
          AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
          ON fk.referenced_column_id = clm2.column_id
          AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(fk.referenced_object_id) = 'My Table'  --- table name which is being referenced by other tables via Foreign Keys

ORDER BY OBJECT_NAME(fk.referenced_object_id)

Below is the output from AdventureWorks database

I hope this would help you at some point in carrying out your DBA or Developer task in understanding the database Schema design.