You are here
Home > DMO

Lost in Translation – Deprecated System Tables – sysforeignkeys

This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objectsYou can find a list of all of the deprecated views in the introduction post.

The compatibility view sysforeignkeys returns information on the columns in foreignkeys.  Each row represents the relationship between each column in foreign key relationships.  With the keyno column indicating the order of the columns within the constraint.

Sysforeignkeys is replaced by the catalog view sys.foreign_key_columns.  Like the compatibility view, the catalog view also represents each row of constraint relationships in it’s output.

Query Via sysforeignkeys

Contrary to many of the other compatibility views, the information represented in sysforeignkeys is done so quite simply.  There are columns identifying the relationship, parent, and child tables.  And then columns for the rows in the parents and columns that are related and their order.  The query in Listing 1 is one commonly used to retrieve information from sysforeignkeys.

--Listing 1 – Query for sys.sysforeignkeys

SELECT constid
FROM sysforeignkeys

Query via sys.foreign_key_columns

As simple as sysforeignkeys is to access, sys.foreign_key_columns is just as easy.  Save for the renaming of the columns, the information returned is identical in all other respects.  Using the query in Listing 2, the same information on foreign keys can be retrieved.  The one thing a little more simple about sys.foreign_key_columns is the added clarity in the name of the catalog view and the names of the columns

--Listing 2 – Query for sys.foreign_key_columns

SELECT constraint_object_id AS constid
,parent_object_id AS fkeyid
,referenced_object_id AS rkeyid
,constraint_column_id AS rkey
,parent_column_id AS fkey
,referenced_column_id AS keyno
FROM sys.foreign_key_columns


In this post, the use of sys.foreign_key_columns was discussed as an alternative to sysforeignkeys.  With a very small amount of code change, the upgrade from one to the other is quite simple.  After reading all of this, do you see any reason to continue using sysforeignkeys?  Is there anything missing from this post that people continuing to use the compatibility view should know?

  • That was the moment I fell in love with her!

  • Pingback: Lost in Translation - Deprecated System Tables - Introduction | Strate SQL()

  • Leo Goldshteyn

    Thank you so much!Sorry, Jason,
    I have just cut, paste, and executed both queries,
    and found trading places situation:
    data under “fkey” in the first record set went under “rkey” in the second record set.

    Best regards,
    Leo Goldshteyn

  • Daniel Gauthier

    Should be (Keyno and fkey are inversed)
    SELECT constraint_object_id AS constid
    ,parent_object_id AS fkeyid
    ,referenced_object_id AS rkeyid
    ,constraint_column_id AS rkey
    ,parent_column_id AS keyno
    ,referenced_column_id AS fkey
    FROM sys.foreign_key_columns