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');
GO

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');
GO

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

Enjoy!