How to use INFORMATION_SCHEMA views In SQL
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.
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.
Contact Us