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.
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.
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...(!)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With