Programmatically picking apart foreign-key constraints

Another quickie today. Don't ask why, but this morning I was trying to programmatically work out which tables have foreign-key references to table Sales, because they're preventing fast partition switching on table Sales.

After more spelunking around I thought I had it with this code:

SELECT * FROM sys.all_objects
WHERE [type] = 'F' AND [parent_object_id] = OBJECT_ID ('Sales');

But that gives me the foreign-key references FROM the Sales table, not TO the Sales table.

Eventually I found the sys.foreign_key_columns table which does the trick, using the following code:

SELECT OBJECT_NAME ([parent_object_id]) AS 'Referencing Table', * FROM sys.foreign_key_columns
WHERE [referenced_object_id] = OBJECT_ID ('Sales');

And it also gives me the column IDs in both tables involved in the constraint. 


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.