A list of all foreign key constraints that apply to a specific column in a specific table

Question

I would like to see a list of all tables and columns that refer (directly or indirectly) to a specific column in the "main" table with a foreign key constraint that does not include the ON DELETE = CASCADE parameter.

The hard part is that there would be indirect relationships, plunged to a depth of up to 5 levels. (example: ... great-grandson-> FK3 => grandchild => FK2 => child => FK1 => main table). We need to dig up leaf column tables, not just one level. The “good” part is that execution speed is not a concern, it will run on a backup copy of the production database to fix any relational problems in the future.

I did SELECT * FROM sys.foreign_keys , but that gives me the name of the constraint - not the names of the child parent tables and columns in relation (juicy bits). Plus, the previous designer used short, non descriptive / random names for FK restrictions, unlike our practice below

A way to add restrictions in SQL Server:

 ALTER TABLE [dbo].[UserEmailPrefs] WITH CHECK ADD CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] FOREIGN KEY([UserId]) REFERENCES [dbo].[UserMasterTable] ([UserId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UserEmailPrefs] CHECK CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] GO 

The comments in this SO question inspired this question.

Answer 1

A wiser version of oneself comes across a question from a curious, younger version of oneself. Answer

EXEC sp_fkeys 'Users'

I hope that from the age of 40 I will leave this week for some additional words of wisdom over the years ahead :)