This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You 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
,fkeyid
,rkeyid
,fkey
,rkey
,keyno
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

Summary

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?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysforeignkeys

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysconstraints
2. Lost in Translation – Deprecated System Tables – sysfulltextcatalogs
3. Lost in Translation – Deprecated System Tables – sysdepends