This article presents eleven different approaches to retrieving foreign key information in SQL Server. Some of these will be more useful than others, depending on the situation.
Some of these methods can be modified to return further information. For example, you could perform joins on various system views to return more data.
Example 1 – The sp_fkeys System Stored Procedure
The sp_fkeys system stored procedure returns logical foreign key information for the current environment. It shows foreign key relationships including disabled foreign keys.
This stored procedure is ideal for when you need foreign key information for a specific table. You simply pass the primary or foreign key table name to the procedure, and it will return the relevant info.
Example:
EXEC sp_fkeys @fktable_name = Albums;
Result (using vertical output):
-[ RECORD 1 ]------------------------- PKTABLE_QUALIFIER | Music PKTABLE_OWNER | dbo PKTABLE_NAME | Artists PKCOLUMN_NAME | ArtistId FKTABLE_QUALIFIER | Music FKTABLE_OWNER | dbo FKTABLE_NAME | Albums FKCOLUMN_NAME | ArtistId KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Albums_Artists PK_NAME | PK__Artists__25706B50FCD918B1 DEFERRABILITY | 7 -[ RECORD 2 ]------------------------- PKTABLE_QUALIFIER | Music PKTABLE_OWNER | dbo PKTABLE_NAME | Genres PKCOLUMN_NAME | GenreId FKTABLE_QUALIFIER | Music FKTABLE_OWNER | dbo FKTABLE_NAME | Albums FKCOLUMN_NAME | GenreId KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Albums_Genres PK_NAME | PK__Genres__0385057E88BB96F8 DEFERRABILITY | 7
Another way of using this view is to provide the primary key table name:
EXEC sp_fkeys @pktable_name = Artists;
Result (using vertical output):
-[ RECORD 1 ]------------------------- PKTABLE_QUALIFIER | Music PKTABLE_OWNER | dbo PKTABLE_NAME | Artists PKCOLUMN_NAME | ArtistId FKTABLE_QUALIFIER | Music FKTABLE_OWNER | dbo FKTABLE_NAME | Albums FKCOLUMN_NAME | ArtistId KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Albums_Artists PK_NAME | PK__Artists__25706B50FCD918B1 DEFERRABILITY | 7
You can also use other arguments, such as the table owner and table qualifier:
EXEC sp_fkeys @pktable_name = Artists, @pktable_owner = dbo, @pktable_qualifier = Music, @fktable_name = Albums, @fktable_owner = dbo, @fktable_qualifier = Music;
Example 2 – The REFERENTIAL_CONSTRAINTS System View
The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS system view returns one row for each FOREIGN KEY constraint in the current database.
This is ideal for quickly returning a list of foreign keys in the database along with their associated unique constraint. You’re not limited to just one table like with the previous example.
Here’s an example:
SELECT
CONSTRAINT_NAME,
UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
Result:
+-------------------+-------------------------------+ | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_NAME | |-------------------+-------------------------------| | FK_Albums_Artists | PK__Artists__25706B50FCD918B1 | | FK_Albums_Genres | PK__Genres__0385057E88BB96F8 | +-------------------+-------------------------------+
So in this case we can see each foreign key in the current database, along with its associated primary key.
As it turns out, this database only has two foreign keys and they’re the same ones we saw in the previous example.
Here’s an example that returns all data for the FK_Albums_Artists foreign key:
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_Albums_Artists';
Result:
CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists UNIQUE_CONSTRAINT_CATALOG | Music UNIQUE_CONSTRAINT_SCHEMA | dbo UNIQUE_CONSTRAINT_NAME | PK__Artists__25706B50FCD918B1 MATCH_OPTION | SIMPLE UPDATE_RULE | NO ACTION DELETE_RULE | NO ACTION
You’ll notice that this method doesn’t provide the primary and foreign key table details that you get with the previous method, so you’d need to do some joins with other views if you need that information (or use one of the methods below).
Example 3 – The KEY_COLUMN_USAGE System View
The INFORMATION_SCHEMA.KEY_COLUMN_USAGE system view returns one row for each column that is constrained as a key in the current database.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Albums | GenreId | FK_Albums_Genres | | Albums | AlbumId | PK__Albums__97B4BE379FC780BD | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | | Genres | GenreId | PK__Genres__0385057E88BB96F8 | +--------------+---------------+-------------------------------+
We can see that this returns all constraints, including primary keys.
We can also add a WHERE clause to narrow the results to a specific table.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Albums';
Result:
+--------------+---------------+------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Albums | GenreId | FK_Albums_Genres | | Albums | AlbumId | PK__Albums__97B4BE379FC780BD | +--------------+---------------+------------------------------+
In these examples, I narrowed the results to just those columns of the view that I’m interested in, but you can also use the asterisk (*) wildcard to return all columns.
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'Albums';
Result (using vertical output):
-[ RECORD 1 ]------------------------- CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | ArtistId ORDINAL_POSITION | 1 -[ RECORD 2 ]------------------------- CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Genres TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | GenreId ORDINAL_POSITION | 1 -[ RECORD 3 ]------------------------- CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Albums__97B4BE379FC780BD TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | AlbumId ORDINAL_POSITION | 1
Example 4 – The CONSTRAINT_COLUMN_USAGE System View
This is similar to the previous example. The INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE system view returns one row for each column in the current database that has a constraint defined on the column.
As with INFORMATION_SCHEMA.KEY_COLUMN_USAGE you’ll also get primary keys returned with this view. However, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE is not limited to just key constraints.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Albums | GenreId | FK_Albums_Genres | | Albums | AlbumId | PK__Albums__97B4BE379FC780BD | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | | Genres | GenreId | PK__Genres__0385057E88BB96F8 | +--------------+---------------+-------------------------------+
You can use a WHERE clause to return only a specific table or column:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE COLUMN_NAME = 'ArtistId';
Result:
+--------------+---------------+-------------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | |--------------+---------------+-------------------------------| | Albums | ArtistId | FK_Albums_Artists | | Artists | ArtistId | PK__Artists__25706B50FCD918B1 | +--------------+---------------+-------------------------------+
In this case I specified a column name, so it returned all constraints on columns with that name. As it turns out, the database uses the same column name in two different tables. One has a foreign key constraint, the other a primary key. Therefore both rows are returned.
Again, you can use the asterisk (*) wildcard to return all columns.
Here’s an example where I return all data for a specific table:
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Albums';
Result (using vertical output):
-[ RECORD 1 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | ArtistId CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists -[ RECORD 2 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | GenreId CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Genres -[ RECORD 3 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums COLUMN_NAME | AlbumId CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Albums__97B4BE379FC780BD
Example 5 – The CONSTRAINT_TABLE_USAGE System View
The INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE system view returns one row for each table in the current database that has a constraint defined on the table.
SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE;
Result:
+--------------+-------------------------------+ | TABLE_NAME | CONSTRAINT_NAME | |--------------+-------------------------------| | Artists | PK__Artists__25706B50FCD918B1 | | Genres | PK__Genres__0385057E88BB96F8 | | Albums | PK__Albums__97B4BE379FC780BD | | Albums | FK_Albums_Artists | | Albums | FK_Albums_Genres | +--------------+-------------------------------+
This view doesn’t return the column name/s that the constraints are used on, so it might not be as useful for you, depending on your requirements.
Here’s an example that shows all data returned by this view for the Albums table:
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE TABLE_NAME = 'Albums';
Result:
-[ RECORD 1 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | PK__Albums__97B4BE379FC780BD -[ RECORD 2 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists -[ RECORD 3 ]------------------------- TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Genres
Example 6 – The TABLE_CONSTRAINTS System View
The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view returns one row for each table constraint in the current database.
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
Result:
+--------------+-------------------------------+-------------------+ | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | |--------------+-------------------------------+-------------------| | Artists | PK__Artists__25706B50FCD918B1 | PRIMARY KEY | | Genres | PK__Genres__0385057E88BB96F8 | PRIMARY KEY | | Albums | PK__Albums__97B4BE379FC780BD | PRIMARY KEY | | Albums | FK_Albums_Artists | FOREIGN KEY | | Albums | FK_Albums_Genres | FOREIGN KEY | +--------------+-------------------------------+-------------------+
This view doesn’t provide the column name, but it does provide the constraint type. Therefore, you can narrow the results to just foreign keys:
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
Result:
+--------------+-------------------+-------------------+ | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | |--------------+-------------------+-------------------| | Albums | FK_Albums_Artists | FOREIGN KEY | | Albums | FK_Albums_Genres | FOREIGN KEY | +--------------+-------------------+-------------------+
Here’s an example that uses a wildcard (*) character to return all data for the foreign key constraints applied to the Albums table:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Albums' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Result:
-[ RECORD 1 ]------------------------- CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Artists TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums CONSTRAINT_TYPE | FOREIGN KEY IS_DEFERRABLE | NO INITIALLY_DEFERRED | NO -[ RECORD 2 ]------------------------- CONSTRAINT_CATALOG | Music CONSTRAINT_SCHEMA | dbo CONSTRAINT_NAME | FK_Albums_Genres TABLE_CATALOG | Music TABLE_SCHEMA | dbo TABLE_NAME | Albums CONSTRAINT_TYPE | FOREIGN KEY IS_DEFERRABLE | NO INITIALLY_DEFERRED | NO
Example 7 – The sys.foreign_keys System View
The sys.foreign_keys system view contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F (“F” is for “Foreign Key”).
Example:
SELECT name AS 'Constraint', OBJECT_NAME(parent_object_id) AS 'Table', OBJECT_NAME(referenced_object_id) AS 'Referenced Table' FROM sys.foreign_keys;
Result:
+-------------------+---------+---------------------+ | Constraint | Table | Referenced Table | |-------------------+---------+---------------------| | FK_Albums_Artists | Albums | Artists | | FK_Albums_Genres | Albums | Genres | +-------------------+---------+---------------------+
Here it is when returning all data for the FK_Albums_Artists foreign key:
SELECT * FROM sys.foreign_keys WHERE name = 'FK_Albums_Artists';
Result:
name | FK_Albums_Artists object_id | 981578535 principal_id | NULL schema_id | 1 parent_object_id | 949578421 type | F type_desc | FOREIGN_KEY_CONSTRAINT create_date | 2019-05-02 12:56:06.380 modify_date | 2019-05-02 12:56:06.380 is_ms_shipped | 0 is_published | 0 is_schema_published | 0 referenced_object_id | 885578193 key_index_id | 1 is_disabled | 0 is_not_for_replication | 0 is_not_trusted | 0 delete_referential_action | 0 delete_referential_action_desc | NO_ACTION update_referential_action | 0 update_referential_action_desc | NO_ACTION is_system_named | 0
Example 8 – The sys.foreign_key_columns System View
The sys.foreign_key_columns system view contains a row for each column, or set of columns, that comprise a foreign key.
Example:
SELECT OBJECT_NAME(constraint_object_id) AS 'Foreign Key', OBJECT_NAME(parent_object_id) AS 'Table', OBJECT_NAME(referenced_object_id) AS 'Referenced Table' FROM sys.foreign_key_columns;
Result:
+--------------------+---------+--------------------+ | Foreign Key | Table | Referenced Table | |--------------------+---------+--------------------| | FK_Albums_Artists | Albums | Artists | | FK_Albums_Genres | Albums | Genres | | FK_Artists_Country | Artists | Country | +--------------------+---------+--------------------+
And here it is when returning all data for the FK_Albums_Artists foreign key:
SELECT * FROM sys.foreign_key_columns WHERE OBJECT_NAME(constraint_object_id) = 'FK_Albums_Artists';
Result:
constraint_object_id | 981578535 constraint_column_id | 1 parent_object_id | 949578421 parent_column_id | 4 referenced_object_id | 885578193 referenced_column_id | 1
Example 9 – The sys.objects System View
The sys.objects system view contains a row for each user-defined, schema-scoped object that is created within a database. So it includes information about our foreign keys.
Example:
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name FROM sys.objects WHERE type = 'F';
Result:
+---------+-------------------+ | Table | name | |---------+-------------------| | Albums | FK_Albums_Artists | | Albums | FK_Albums_Genres | +---------+-------------------+
In this example I narrowed the results to just foreign keys (type F).
Here’s an example that returns all data for the FK_Albums_Artists foreign key:
SELECT * FROM sys.objects WHERE name = 'FK_Albums_Artists';
Result (using vertical output):
-[ RECORD 1 ]------------------------- name | FK_Albums_Artists object_id | 981578535 principal_id | NULL schema_id | 1 parent_object_id | 949578421 type | F type_desc | FOREIGN_KEY_CONSTRAINT create_date | 2019-05-02 12:56:06.380 modify_date | 2019-05-02 12:56:06.380 is_ms_shipped | 0 is_published | 0 is_schema_published | 0
Example 10 – The sp_helpconstraint System Stored Procedure
The sp_helpconstraint system stored procedure returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only).
Therefore you can use it to return information on foreign keys in your database.
Example:
EXEC sp_helpconstraint Albums;
Result:
+---------------+ | Object Name | |---------------| | Albums | +---------------+ -[ RECORD 1 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Artists delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | ArtistId -[ RECORD 2 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Artists (ArtistId) -[ RECORD 3 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Genres delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | GenreId -[ RECORD 4 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Genres (GenreId) -[ RECORD 5 ]------------------------- constraint_type | PRIMARY KEY (clustered) constraint_name | PK__Albums__97B4BE379FC780BD delete_action | (n/a) update_action | (n/a) status_enabled | (n/a) status_for_replication | (n/a) constraint_keys | AlbumId
Example 11 – The sp_help System Stored Procedure
The sp_help system stored procedure reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. This includes information about foreign keys.
When you run this without any arguments, it provides information about summary information of objects of all types that exist in the current database is returned (i.e. a lot of information).
However, you can provide the name of a table, and it will return information about that table. Again, you’ll get a lot of information returned, but amongst it will be foreign key information.
Example:
EXEC sp_help Albums;
Result:
+--------+---------+------------+-------------------------+ | Name | Owner | Type | Created_datetime | |--------+---------+------------+-------------------------| | Albums | dbo | user table | 2019-05-02 12:56:06.370 | +--------+---------+------------+-------------------------+ -[ RECORD 1 ]------------------------- Column_name | AlbumId Type | int Computed | no Length | 4 Prec | 10 Scale | 0 Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL -[ RECORD 2 ]------------------------- Column_name | AlbumName Type | nvarchar Computed | no Length | 510 Prec | Scale | Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | SQL_Latin1_General_CP1_CI_AS -[ RECORD 3 ]------------------------- Column_name | ReleaseDate Type | date Computed | no Length | 3 Prec | 10 Scale | 0 Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL -[ RECORD 4 ]------------------------- Column_name | ArtistId Type | int Computed | no Length | 4 Prec | 10 Scale | 0 Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL -[ RECORD 5 ]------------------------- Column_name | GenreId Type | int Computed | no Length | 4 Prec | 10 Scale | 0 Nullable | no TrimTrailingBlanks | (n/a) FixedLenNullInSource | (n/a) Collation | NULL +------------+--------+-------------+-----------------------+ | Identity | Seed | Increment | Not For Replication | |------------+--------+-------------+-----------------------| | AlbumId | 1 | 1 | 0 | +------------+--------+-------------+-----------------------+ +-------------------------------+ | RowGuidCol | |-------------------------------| | No rowguidcol column defined. | +-------------------------------+ +-----------------------------+ | Data_located_on_filegroup | |-----------------------------| | PRIMARY | +-----------------------------+ +------------------------------+---------------------------------------------------+--------------+ | index_name | index_description | index_keys | |------------------------------+---------------------------------------------------+--------------| | PK__Albums__97B4BE379FC780BD | clustered, unique, primary key located on PRIMARY | AlbumId | +------------------------------+---------------------------------------------------+--------------+ -[ RECORD 1 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Artists delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | ArtistId -[ RECORD 2 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Artists (ArtistId) -[ RECORD 3 ]------------------------- constraint_type | FOREIGN KEY constraint_name | FK_Albums_Genres delete_action | No Action update_action | No Action status_enabled | Enabled status_for_replication | Is_For_Replication constraint_keys | GenreId -[ RECORD 4 ]------------------------- constraint_type | constraint_name | delete_action | update_action | status_enabled | status_for_replication | constraint_keys | REFERENCES Music.dbo.Genres (GenreId) -[ RECORD 5 ]------------------------- constraint_type | PRIMARY KEY (clustered) constraint_name | PK__Albums__97B4BE379FC780BD delete_action | (n/a) update_action | (n/a) status_enabled | (n/a) status_for_replication | (n/a) constraint_keys | AlbumId