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!
6 thoughts on “Programmatically picking apart foreign-key constraints”
Great timing – was just needing to do this and saw your post. Worked perfectly. Thanks
And in case you have composite foreign keys and you want to get rid of duplicates, just use sys.foreign_keys…
And if you’re ruthless and brave and you want to get rid of these foreign keys automatically
http://michaeljswart.com/?p=707
Or if you want to see everything downstream
http://jasonbrimhall.info/2010/02/01/key-discovery-iii/
Very cool. You coerced me into spelunking furhter as to how how I might quickly peruse all relationships for annomolies. This did it for me.
/*
Robert Magrogan, Robert@MyRamLink.com October 9 2010
Ref: SQLSkills and Michael Stwart https://www.sqlskills.com/blogs/paul/post/Programmatically-picking-apart-foreign-key-constraints.aspx
Get all relationships (Foreign keys contraingts) for a given table
*/
SELECT
OBJECT_NAME(fkc.referenced_object_id) AS ReferencedObject
, COL_NAME(fkc.referenced_object_id , fkc.referenced_column_id) AS ‘ReferencedColumn’
, COL_NAME(fkc.parent_object_id , fkc.parent_column_id) AS ‘ReferencingColumnName’
, OBJECT_NAME(fkc.parent_object_id) AS ‘ReferencingObjectName’
, OBJECT_NAME(fkc.constraint_object_id) AS ‘ForeignKeyName’
, FK.is_not_for_replication
, FK.delete_referential_action_desc
, fk.update_referential_action_desc
, fk.schema_id
FROM sys.foreign_key_columns FKC
INNER JOIN sys.foreign_keys FK ON FKC.constraint_object_id=FK.object_id
INNER JOIN sys.schemas S ON FK.schema_id=S.schema_id
–WHERE [referenced_object_id]=OBJECT_ID(‘tblPersons’)
ORDER BY 1,2,4,5
–SELECT TOP 2 * FROM sys.schemas
–SELECT TOP 2 * FROM sys.foreign_key_columns
–SELECT TOP 2 * FROM sys.foreign_keys
Here’s my variation on Robert’s script to include the schema name in both referenced and referencing columns:
SELECT rs.name + ‘.’ + r.name AS ‘This table’,
rc.name AS ‘and Column’,
ps.name + ‘.’ + p.name AS ‘Is referenced by this table’,
pc.name AS ‘and by this Column’,
OBJECT_NAME(fk.constraint_object_id) AS ‘in Constraint’
FROM sys.foreign_key_columns fk
join sys.objects p
on fk.parent_object_id = p.object_id
join sys.schemas ps
on p.schema_id = ps.schema_id
join sys.columns pc
on fk.parent_object_id = pc.object_id
and fk.parent_column_id = pc.column_id
join sys.objects r
on fk.referenced_object_id = r.object_id
join sys.schemas rs
on r.schema_id = rs.schema_id
join sys.columns rc
on fk.referenced_object_id = rc.object_id
and fk.referenced_column_id = rc.column_id
WHERE fk.referenced_object_id = OBJECT_ID (‘wf.department’)
–WHERE rs.name = ‘WF’ — to see all foreign key relationships for a schema
order by rs.name, r.name, rc.name, ps.name, p.name;
GO