How to Find Foreign Key References Using SQL Server's information_schema?

Getting Foreign Key Reference from information_schema in SQL Server

Question:

In SQL Server, how can you determine the referenced table and column name associated with a foreign key?

Answer:

The information_schema provides valuable metadata about database objects, including foreign key relationships. To retrieve the referenced table and column information, you can use the following query:

SELECT 
     KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA 
    ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 

Additional Considerations:

• Unique Constraints: Information_schema does not track indices, only unique constraints. To find foreign keys based on unique indices, you can query Microsoft proprietary tables such as sys.foreign_keys and sys.key_constraints.
• Proof Test: The provided edge-case test demonstrates how to correctly identify referenced table and column information in various scenarios involving foreign key constraints and unique indices.

The above is the detailed content of How to Find Foreign Key References Using SQL Server's information_schema?. For more information, please follow other related articles on the PHP Chinese website!