Michael J. Swart

May 23, 2012

The View of Foreign Keys That I Wanted

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 8:47 pm

tropical sunset

So I’ve been working on an automation project that makes frequent use of foreign key metadata. I find myself writing queries for this data but I discovered that there’s no super-easy out-of-the-box view of foreign keys for me to use. Here are the ones I considered.

INFORMATION_SCHEMA views

INFORMATION_SCHEMA views that give me foreign key information include

  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the foreign key info
  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for the referring columns,
  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for the referenced columns

The first view here gives a list of foreign keys and you have to join to the other two tables in order to find the column names. But it’s a crummy solution. First of all, if the foreign key has multiple columns, there’s no real way to match the referring columns to the referenced columns.

The second thing is that we don’t see foreign keys that point to unique Keys (as pointed out by Aaron Bertrand in his post The case against INFORMATION_SCHEMA views.

So that’s out. What else have we got in?

Microsoft SQL Server system views

These views include

  • sys.foreign_keys
  • sys.foreign_key_columns
  • with support from sys.columns and sys.tables

These are the views I deserve, but not the views I need right now. The joins are just too annoying to remember and type each time.

Besides, the word “parent” used here changes with context. The parent table in a foreign key relationship owns the foreign key and does the pointing. But say I’m modeling a hierarchy. In the context of the data model, children records point to their parent records. The mental effort needed to keep these straight is not difficult, but it’s annoying.

My Own Views

So I’ve created my own, the goal is to simplify typing and minimize joins. I skip the word “parent” all together and use “referrer” and “referrenced”. Feel free to use and build on these.

Update June 7, 2012: I added columns ReferrerColumnCanBeNull to each view. I found I wanted it, so I added it here.

FOREIGN_KEYS

CREATE VIEW dbo.FOREIGN_KEYS
AS
    SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
            fk.name AS FK ,
            SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
            p.name AS Referrer ,
            STUFF(CAST(( SELECT ',' + c.name
                         FROM   sys.foreign_key_columns fkc
                                JOIN sys.columns c ON fkc.parent_object_id = c.object_id
                                                      AND fkc.parent_column_id = c.column_id
                         WHERE  fkc.constraint_object_id = fk.object_id
                         ORDER BY fkc.constraint_column_id ASC
                       FOR
                         XML PATH('') ,
                             TYPE
                       ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferrerColumns ,
            ISNULL(( SELECT TOP 1
                            1
                     FROM   sys.foreign_key_columns fkc
                            JOIN sys.columns c ON fkc.parent_object_id = c.object_id
                                                  AND fkc.parent_column_id = c.column_id
                     WHERE  fkc.constraint_object_id = fk.object_id
                            AND c.is_nullable = 1
                   ), 0) AS ReferrerColumnsCanBeNull ,
            SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
            r.name AS Referenced ,
            STUFF(CAST(( SELECT ',' + c.name
                         FROM   sys.foreign_key_columns fkc
                                JOIN sys.columns c ON fkc.referenced_object_id = c.object_id
                                                      AND fkc.referenced_column_id = c.column_id
                         WHERE  fkc.constraint_object_id = fk.object_id
                         ORDER BY fkc.constraint_column_id ASC
                       FOR
                         XML PATH('') ,
                             TYPE
                       ) AS NVARCHAR(MAX)), 1, 1, '') AS ReferencedColumns ,
            fk.delete_referential_action_desc AS deleteAction ,
            fk.update_referential_action_desc AS updateAction ,
            fk.object_id AS FKId ,
            p.object_id AS ReferrerId ,
            r.object_id AS ReferencedId
    FROM    sys.foreign_keys fk
            JOIN sys.tables p ON p.object_id = fk.parent_object_id
            JOIN sys.tables r ON r.object_id = fk.referenced_object_id
GO

FOREIGN_KEY_COLUMNS

CREATE VIEW dbo.FOREIGN_KEY_COLUMNS
AS
SELECT  SCHEMA_NAME(fk.schema_id) AS FKSchema ,
        fk.name AS FK ,
        SCHEMA_NAME(p.schema_id) AS ReferrerSchema ,
        p.name AS Referrer ,
        pc.name AS ReferrerColumn ,
		pc.is_nullable AS ReferrerColumnCanBeNull ,
        SCHEMA_NAME(r.schema_id) AS ReferencedSchema ,
        r.name AS Referenced,
        rc.name AS ReferencedColumn ,
        fk.object_id AS FKId ,
        fkc.constraint_column_id AS FKColumnId ,
        p.object_id AS ReferrerId ,
        r.object_id AS ReferencedId
FROM    sys.foreign_keys fk
        JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        JOIN sys.tables p ON p.object_id = fk.parent_object_id
        JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id
                               AND fkc.parent_column_id = pc.column_id
        JOIN sys.tables r ON r.object_id = fk.referenced_object_id
        JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id
                               AND fkc.referenced_column_id = rc.column_id
GO

13 Comments »

  1. [...] The View of Foreign Keys That I Wanted - Michael J. Swart (Blog|Twitter) [...]

    Pingback by Something for the Weekend - SQL Server Links 25/05/12 — May 25, 2012 @ 11:33 am

  2. Are you at liberty to describe the “automation project” for which you need foreign key information?

    Comment by DBAdmin — May 26, 2012 @ 5:12 pm

  3. Unfortunately not DBAdmin. And it’s one of the greatest tragedies of this century because it’s a really interesting project with a whole slew of interesting challenges.

    Comment by Michael J. Swart — May 26, 2012 @ 5:53 pm

  4. Bummer! The foreign key information can be used for some very interesting purposes. I have used it for generating SQL code to do the same thing as Red Gate’s SQL Data Compare. You can execute the generated SQL code over and over, without having to go through the GUI over and over. I have also used it for generating SQL code to manipulate a set of related rows in related tables, as a unit. The generated SQL code can have an effect like ON DELETE CASCADE, but it can also do things like copying a set of related rows from related tables into a demo database.

    Comment by DBAdmin — May 26, 2012 @ 8:36 pm

  5. [...] Here is a cool Foreign Key Query I got from http://michaeljswart.com/2012/05/the-view-of-foreign-keys-that-i-wanted/ [...]

    Pingback by Foreign Key Query | Julius Nyoto — May 29, 2012 @ 1:05 am

  6. [...] The View of Foreign Keys That I Wanted [...]

    Pingback by Link Resource # 56 : May 24 – May 30 « Dactylonomy of Web Resource — May 29, 2012 @ 2:27 am

  7. The lack of a good way to get the foreign keys always baffles me. We will have issues if the ability to roll your own views is hampered by underlying system changes. The upside is all the other nice things you’re able to infer fron tge schema information.

    Comment by emmett childress — June 2, 2012 @ 3:08 pm

  8. [...] Michael Swart, who has a couple of views showing foreign key metadata so you don’t have to re-create the [...]

    Pingback by INFORMATION_SCHEMA And Standards « 36 Chambers – The Legendary Journeys: Execution to the max! — June 2, 2012 @ 6:01 pm

  9. It seems like many people are interested in foreign key metadata. How about sharing some ideas for how it’s used? I use it for a lot of SQL code generation and I gave a couple suggestions (see above). What are others doing with it?

    Comment by DBAdmin — June 2, 2012 @ 9:59 pm

  10. In addition to sql code I also generate c# .net code. Bassically any code that is schema based I generate from a view. I havent generated unit tests yet.

    Comment by emmett childress — June 3, 2012 @ 11:06 am

  11. Generating SQL code is my own most recent use. Specifically, I have the task of deleting a large portion of a database in batches without being able to (or not wanting to) rely on referential actions like “ON CASCADE DELETE”. It sounds a bit like your project DBAdmin. I hope that makes some sense.

    Here are a couple other scripts I’ve published before where I could have benefited from these views. Indexing Foreign Keys and Disowning Your Relatives.

    Comment by Michael J. Swart — June 3, 2012 @ 11:33 am

  12. Michael
    A nice article, I also find FK are not being used effectively as well as the other constraints..
    FYI – I’m doing a virtual session on “Constraints” including FK pretty soon {19th June 2012} for the DataArch Virtual Chapter details @ DataArch.sqlpass.org

    Comment by Neil Hambly — June 6, 2012 @ 4:50 am

  13. Update June 7, 2012: I added columns ReferrerColumnCanBeNull to each foreign key view. I found I wanted it, so I added it here in the post.

    Comment by Michael J. Swart — June 7, 2012 @ 2:34 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress