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 sysreferences returns a row for every foreign key constraint in a database.  The constraint includes the base data for the foreign key along with a denormalized view of the sixteen possible columns that can be included in a foreign key.

The compatibility view is replaced by two catalog view sys.foreign_keys and sys.foreign_key_columns.  The output from sys.foreign keys returns one row for every foreign key, including some information, such as name, which was not previously available in the view.  The columns that make up the foreign key are now normalized and returned in the catalog view sys.foreign_key_columns.

Query Via sysreferences

Using sysreferences is a fairly simple task.  With the foreign key columns denomarlized into the compatibility view, there is not much to retrieving the foreign key information.  Out of all of the columns, there are four that no longer have a purpose, as they now only return values of either NULL or 0.  These columns are forkeys, refkeys, fkeydbid, and rkeydbid.  The query provided in Listing 1 returns foreign key information from the compatibility view.

--Listing 1 – Query for sys.sysreferences
SELECT  constid
,fkeyid
,rkeyid
,rkeyindid
,keycnt
,forkeys
,refkeys
,fkeydbid
,rkeydbid
,fkey1
,fkey2
,fkey3
,fkey4
,fkey5
,fkey6
,fkey7
,fkey8
,fkey9
,fkey10
,fkey11
,fkey12
,fkey13
,fkey14
,fkey15
,fkey16
,rkey1
,rkey2
,rkey3
,rkey4
,rkey5
,rkey6
,rkey7
,rkey8
,rkey9
,rkey10
,rkey11
,rkey12
,rkey13
,rkey14
,rkey15
,rkey16
FROM sysreferences

Query via sys.foreign_keys and sys.foreign_key_columns

Querying the catalog views, sys.foreign_keys and sys.foreign_key_columns, for foreign key information is bit more complicated when trying to replicate the data from the compatibility view.  One of the chief differences comes from the fact that two catalog views replace the former view.  In order to replicate the output, the data from sys.foreign_key_columns needs to be pivoted and summarized on a per column basis, as is provided in the query in Listing 2.  The other difference is the change in the names of the columns between the views.

--Listing 2 – Query for sys.foreign_keys
SELECT  fk.object_id AS constid
,fk.parent_object_id AS fkeyid
,fk.referenced_object_id AS rkeyid
,fk.key_index_id AS rkeyindid
,fkc.keycnt
,fkc.fkey1
,fkc.fkey2
,fkc.fkey3
,fkc.fkey4
,fkc.fkey5
,fkc.fkey6
,fkc.fkey7
,fkc.fkey8
,fkc.fkey9
,fkc.fkey10
,fkc.fkey11
,fkc.fkey12
,fkc.fkey13
,fkc.fkey14
,fkc.fkey15
,fkc.fkey16
,fkc.rkey1
,fkc.rkey2
,fkc.rkey3
,fkc.rkey4
,fkc.rkey5
,fkc.rkey6
,fkc.rkey7
,fkc.rkey8
,fkc.rkey9
,fkc.rkey10
,fkc.rkey11
,fkc.rkey12
,fkc.rkey13
,fkc.rkey14
,fkc.rkey15
,fkc.rkey16
,fk.name
,fk.principal_id
,fk.schema_id
,fk.type
,fk.type_desc
,fk.create_date
,fk.modify_date
,fk.is_ms_shipped
,fk.is_published
,fk.is_schema_published
,fk.is_disabled
,fk.is_not_for_replication
,fk.is_not_trusted
,fk.delete_referential_action
,fk.delete_referential_action_desc
,fk.update_referential_action
,fk.update_referential_action_desc
,fk.is_system_named
FROM sys.foreign_keys fk
OUTER APPLY (SELECT COUNT(DISTINCT c.constraint_column_id) AS keycnt
,MAX(CASE WHEN c.constraint_column_id = 1 THEN c.parent_column_id ELSE 0 END) AS fkey1
,MAX(CASE WHEN c.constraint_column_id = 2 THEN c.parent_column_id ELSE 0 END) AS fkey2
,MAX(CASE WHEN c.constraint_column_id = 3 THEN c.parent_column_id ELSE 0 END) AS fkey3
,MAX(CASE WHEN c.constraint_column_id = 4 THEN c.parent_column_id ELSE 0 END) AS fkey4
,MAX(CASE WHEN c.constraint_column_id = 5 THEN c.parent_column_id ELSE 0 END) AS fkey5
,MAX(CASE WHEN c.constraint_column_id = 6 THEN c.parent_column_id ELSE 0 END) AS fkey6
,MAX(CASE WHEN c.constraint_column_id = 7 THEN c.parent_column_id ELSE 0 END) AS fkey7
,MAX(CASE WHEN c.constraint_column_id = 8 THEN c.parent_column_id ELSE 0 END) AS fkey8
,MAX(CASE WHEN c.constraint_column_id = 9 THEN c.parent_column_id ELSE 0 END) AS fkey9
,MAX(CASE WHEN c.constraint_column_id = 10 THEN c.parent_column_id ELSE 0 END) AS fkey10
,MAX(CASE WHEN c.constraint_column_id = 11 THEN c.parent_column_id ELSE 0 END) AS fkey11
,MAX(CASE WHEN c.constraint_column_id = 12 THEN c.parent_column_id ELSE 0 END) AS fkey12
,MAX(CASE WHEN c.constraint_column_id = 13 THEN c.parent_column_id ELSE 0 END) AS fkey13
,MAX(CASE WHEN c.constraint_column_id = 14 THEN c.parent_column_id ELSE 0 END) AS fkey14
,MAX(CASE WHEN c.constraint_column_id = 15 THEN c.parent_column_id ELSE 0 END) AS fkey15
,MAX(CASE WHEN c.constraint_column_id = 16 THEN c.parent_column_id ELSE 0 END) AS fkey16
,MAX(CASE WHEN c.constraint_column_id = 1 THEN c.referenced_column_id ELSE 0 END) AS rkey1
,MAX(CASE WHEN c.constraint_column_id = 2 THEN c.referenced_column_id ELSE 0 END) AS rkey2
,MAX(CASE WHEN c.constraint_column_id = 3 THEN c.referenced_column_id ELSE 0 END) AS rkey3
,MAX(CASE WHEN c.constraint_column_id = 4 THEN c.referenced_column_id ELSE 0 END) AS rkey4
,MAX(CASE WHEN c.constraint_column_id = 5 THEN c.referenced_column_id ELSE 0 END) AS rkey5
,MAX(CASE WHEN c.constraint_column_id = 6 THEN c.referenced_column_id ELSE 0 END) AS rkey6
,MAX(CASE WHEN c.constraint_column_id = 7 THEN c.referenced_column_id ELSE 0 END) AS rkey7
,MAX(CASE WHEN c.constraint_column_id = 8 THEN c.referenced_column_id ELSE 0 END) AS rkey8
,MAX(CASE WHEN c.constraint_column_id = 9 THEN c.referenced_column_id ELSE 0 END) AS rkey9
,MAX(CASE WHEN c.constraint_column_id = 10 THEN c.referenced_column_id ELSE 0 END) AS rkey10
,MAX(CASE WHEN c.constraint_column_id = 11 THEN c.referenced_column_id ELSE 0 END) AS rkey11
,MAX(CASE WHEN c.constraint_column_id = 12 THEN c.referenced_column_id ELSE 0 END) AS rkey12
,MAX(CASE WHEN c.constraint_column_id = 13 THEN c.referenced_column_id ELSE 0 END) AS rkey13
,MAX(CASE WHEN c.constraint_column_id = 14 THEN c.referenced_column_id ELSE 0 END) AS rkey14
,MAX(CASE WHEN c.constraint_column_id = 15 THEN c.referenced_column_id ELSE 0 END) AS rkey15
,MAX(CASE WHEN c.constraint_column_id = 16 THEN c.referenced_column_id ELSE 0 END) AS rkey16
FROM sys.foreign_key_columns c
WHERE fk.object_id = c.constraint_object_id
GROUP BY c.constraint_object_id) fkc

One of the useful aspects of the catalog views is the additional information that they often provide.  With sys.foreign_keys there are a number of new columns provided.  These new columns include the name and create data for the foreign key.  There are columns identifying whether the constraint can be trusted and whether if is disable.  And, also, information on the referential action on updates and deletes is stored in the catalog view.

Summary

In this post, we compared the compatibility view sysreferences with the catalog views sys.foreign_keys and sys.foreign_key_columns.  There is a bit of additional work to access all of the same information from the compatibility view, but that effort seems worth it with the additional information that the catalog views provide.  After reading all of this, do you see any reason to continue using sysreferences?  Is there anything missing from this post that people continuing to use the compatibility view should know?