I would like to see a list of all the tables and columns that refer (either directly or indirectly) a specific column in the 'main' table via a foreign key constraint that has the ON DELETE=CASCADE setting missing.
The tricky part is that there would be an indirect relationships buried across up to 5 levels deep. (example: ... great-grandchild-> FK3 => grandchild => FK2 => child => FK1 => main table). We need to dig up the leaf tables-columns, not just the very 1st level. The 'good' part about this is that execution speed isn't of concern, it'll be run on a backup copy of the production db to fix any relational issues for 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 the columns in the relationship (the juicy bits). Plus the previous designer used short, non-descriptive/random names for the FK constraints, unlike our practice below
The way we're adding constraints into 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.
