How to List all Foreign Keys Referencing a Given Table in SQL Server?

SQL Server is a Relational Database Management System(RDBMS) that allows users to create and manage databases efficiently. In SQL Server, understanding the table’s relationship is very important for database design and maintenance.

Foreign keys play an important role in establishing relations between different tables. In this article, we will learn how to list all the foreign keys referencing a given table in SQL Server.

When working with SQL Server databases it is very important to understand the relationship between the different tables. As we know a foreign key is a field in one table that uniquely identifies a row of another table, the table containing the foreign key is called the child table and the table that the foreign key refers to is called the parent table. The foreign key implements referential integrity between the table to ensure consistent data.

To list all foreign keys referencing a given table in the SQL server we can use the following methods:

1. Using sp_fkeys stored procedure.
2. Using INFORMATION_SCHEMA views.

Let’s set up an environment:

To understand how we can list all foreign keys referencing a given table in SQL Server, we will consider the following tables Cities, TouristSpots, and Residents. The table Cities is referenced by the City_ref_Id foreign key from both the tables.

Table-Cities

Table-TouristSpots

Table-Residents

The sp_fkeys is a stored procedure present in each database of the SQL Server that stores information about the foreign key relationships for a specified table in the current database. We can query this stored procedure to list all the foreign keys referencing a given table in SQL Server. Following is the syntax to use sp_fkeys stored procedure:

Syntax

EXEC sp_fkeys 'Table_name'

where:

• EXEC: is a statement used to execute the stored procedure sp_keys.
• Table_Name: is the name of the table for which we want to list all of the referencing foreign keys.

Example

To list all the foreign keys referencing the above table Cities we will have to run the following query:

Query:

EXEC sp_fkeys 'Cities'

Output:

Note:The above query returns a single table which has been broken into two parts inorder to demonstrate the output in a better way.

Explanation:The above query returns a table maintained by the stored procedure sp_fkeys. The table consists of many columns out of which the columns FKTABLE_NAME and FKCOLUMN_NAME represents the name of the foreign keys and their respective tables that refers to the table Cities.

In SQL Server, INFORMATION_SCHEMA views is a collection of views stored in the INFORMATION_SCHEMA database that allows the users to get metadata about the tables stored in the database. We can query this view to list all the foreign keys referencing a given table in SQL Server. Following is the syntax to use the INFORMATION_SCHEMA views to list all the referencing foreign keys:

Syntax

SELECT    KCU1.TABLE_NAME AS ReferencingTableName,    
                 KCU1.COLUMN_NAME AS ReferencingColumnName,    
                 KCU2.TABLE_NAME AS ReferencedTableName,    
                 KCU2.COLUMN_NAME AS ReferencedColumnName

FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON RC.CONSTRAINT_NAME = KCU1.CONSTRAINT_NAME

INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON RC.UNIQUE_CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME

WHERE     KCU2.TABLE_NAME = 'YourTableName'; 

where:

• KCU: is KEY_COLUMN_USAGE view that returns information about the columns that are defined as key columns, such as those used in primary keys or foreign keys.
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS: is the name of the INFORAMTION_SCHEMA view that stores details about various constraints such as foreign key in SQL server.
• YourTableName: is the name of the table for which we want to list all of the referencing foreign keys.

Example

To list all the foreign keys referencing the above table Cities using INFORMATION_SCHEMA views we will have to run the following query:

Query:

SELECT    KCU1.TABLE_NAME AS ReferencingTableName,    
                 KCU1.COLUMN_NAME AS ReferencingColumnName,    
                 KCU2.TABLE_NAME AS ReferencedTableName,    
                 KCU2.COLUMN_NAME AS ReferencedColumnName
FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON RC.CONSTRAINT_NAME = KCU1.CONSTRAINT_NAME

INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON RC.UNIQUE_CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME

WHERE     KCU2.TABLE_NAME = 'Cities'; 

Output:

Explanation:The above query returns an output table having 4 columns where the first column ReferencingTableName represents the name of the table to which the foreign keys belongs, the second column ReferencingColumnName represents the name of the foreign keys that refers to the table Cities, the third column ReferencedTableName represents the name of the table to which foreign keys refer to and the last column ReferencedColumnName represents the name of the columns of the main table that acts as the primary keys.

In this article, we have learned how we can list all foreign keys referencing a given table in SQL Server using sp_fkeys and INFORMATION_SCHEMA views. Genreally when you are working with huge databases which consist of many tables it becomes difficult to find out the relationship between the tables. By finding all the foreign keys that refers to a specific table we can easily find out the relationships between them.