List all foreign key constraints that refer to a particular column in a specific table
Posted
by
Sid
on Stack Overflow
See other posts from Stack Overflow
or by Sid
Published on 2012-11-23T19:40:27Z
Indexed on
2012/11/23
23:04 UTC
Read the original article
Hit count: 179
sql-server
|foreign-keys
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 inpire this question.
© Stack Overflow or respective owner