列出引用特定表中特定列的所有外键约束 [英] List all foreign key constraints that refer to a particular column in a specific table

问题描述

我希望看到所有通过缺省ON DELETE = CASCADE设置的外键约束引用（直接或间接）main表中特定列的所有表和列的列表。 p>

棘手的部分是将会有一个间接的关系被埋在5层深处。 （例如：...曾孙 - > FK3 =>孙辈=> FK2 =>小孩=> FK1 =>主表）。我们需要挖掘叶桌，而不仅仅是第一级。关于这一点的好部分是执行速度不受关注，它将运行在生产分贝的备份副本上，以解决未来的任何关系问题。

我做了 SELECT * FROM sys.foreign_keys ，但是这给了我约束的 - 不是父子的名字关系中的表和列（多汁的位）。另外，前面的设计人员使用了短的，非描述性的/随机的名字作为FK的约束，与我们下面的练习不同

我们向SQL Server添加约束的方式： （用户ID）

$ b $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' b $ b REFERENCES [dbo]。[UserMasterTable]（[UserId]）
ON DELETE CASCADE
GO

ALTER TABLE [dbo]。[UserEmailPrefs] CHECK CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId]
GO

this so question 启发了这个问题。

$ b $ EXEC sp_fkeys'Users'

希望我能从今年的+40年下降到未来几年的一些额外的智慧词：

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.

解决方案

A wiser version of myself stumbles upon a question from the curious, younger version of myself. The answer is

EXEC sp_fkeys 'Users'

Hoping me from +40 years drop by this week for some additional words of wisdom for the years ahead :)

这篇关于列出引用特定表中特定列的所有外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！