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.