Query to find all FK constraints, and their referenced table columns

Question

I have a large SQL 2012 database (100 tables), in which I need to find all constraints where the referential action is set to CASCADE (update or delete).

In this useful answer I see that I can list the constraints using the following T-SQL (adapted slightly):

SELECT
    name,
    delete_referential_action_desc,
    update_referential_action_desc
FROM
    sys.foreign_keys
WHERE
    update_referential_action_desc = 'CASCADE' OR
    delete_referential_action_desc = 'CASCADE';

This provides a useful list:

name        delete_referential_action_desc      update_referential_action_desc
----------------------------------------------------------------------
FK_name001  CASCADE                             CASCADE
FK_name002  CASCADE                             NO_ACTION
FK_name003  CASCADE                             NO_ACTION

However, is it possible to expand the code, so that for each constraint the respective table names and columns names are included, e.g.?

name        delete_referential_action_desc      update_referential_action_desc  ParentTable ParentCol   ChildTable  ChildCol
----------------------------------------------------------------------------------------------------------------------------
FK_name001  CASCADE                             CASCADE                         Table1      Col1        Table2      Col2

I've tried different edits pulling in values from sys.objects but cannot figure out how it works, or whether that's even the correct approach.

Update

Please note that I do not want to ALTER anything, or make any schema changes. I just want to view a list so that I can perform further actions manually. I do not have the confidence or experience to edit this gigantic query to pull out the salient points.

The second query in the duplicate will do this, you just need to add the where clause you've already identified to restrict the output to cascading keys. — Aaron Bertrand, Mar 16 '15 at 16:57
But I don't want to `Alter` anything, I just want to *see* them — EvilDr, Mar 16 '15 at 17:54
I understand that. The script can be altered slightly to output the definitions without actually running them. — Aaron Bertrand, Mar 16 '15 at 18:19

score 16 · Accepted Answer · edited May 23 '17 at 11:56

After two days of experimenting I've ~~got pretty close to what I need~~ finally solved it, using this answer as a basis:

SELECT
    C.CONSTRAINT_NAME,
    PK.TABLE_NAME,
    CCU.COLUMN_NAME,
    FK.TABLE_NAME,
    CU.COLUMN_NAME,
    C.UPDATE_RULE,
    C.DELETE_RULE
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON PK.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE
    ((C.UPDATE_RULE = 'CASCADE') OR (C.DELETE_RULE = 'CASCADE')) AND
    (FK.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY
    PK.TABLE_NAME, 
    FK.TABLE_NAME;

Praise be to the digital Gods...(!)

Thank you for going to the effort to get it right. Since the new temporal tables may not have ON UPDATE CASCADE or DELETE, I had to find any such monsters in my big database. **Super-useful**!! — Reversed Engineer, May 31 '19 at 09:12

Query to find all FK constraints, and their referenced table columns

Update

1 Answers1