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

  1. And in case you have composite foreign keys and you want to get rid of duplicates, just use sys.foreign_keys…

  2. 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

  3. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

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.