How can I list all foreign keys referencing a given table in SQL Server?

System stored procedures: Instant foreign key list

For an instant snapshot of foreign keys referencing your table, use the stored procedure sp_fkeys:

EXEC sp_fkeys @pktable_name = 'YourTable', @pktable_owner = 'dbo' -- Replace with your schema and table name


This outputs the names of the referencing tables, columns, and other details. Including @pktable_owner ensures accuracy when working with multiple schemas.

Scripting for thorough understanding

For a more detailed understanding of foreign key relationships, a comprehensive SQL script can be helpful:

SELECT 
    o.name AS FK_name,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    SCHEMA_NAME(rt.schema_id) AS ReferencedSchemaName,
    rt.name AS ReferencedTableName,
    rc.name AS ReferencedColumnName
FROM 
    sys.foreign_key_columns fk
INNER JOIN 
    sys.objects o ON fk.constraint_object_id = o.object_id
INNER JOIN 
    sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN 
    sys.columns c ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id
INNER JOIN 
    sys.tables rt ON fk.referenced_object_id = rt.object_id
INNER JOIN 
    sys.columns rc ON fk.referenced_object_id = rc.object_id AND fk.referenced_column_id = rc.column_id
WHERE 
    rt.name = 'YourTable'  -- Replace with your table's name
ORDER BY 
    SchemaName, TableName, FK_name, ColumnName; -- A sorting order for easy maintenance


This script provides an in-depth view of foreign keys with specific column mappings and ordered for maintenance convenience.

Pre-modification precautions

Before attempting disruptive changes such as dropping a table, it's crucial to observe dependencies to maintain referential integrity:

EXEC sp_help 'YourTable'; -- Nothing hurts more than a lost dependency


This stored procedure gives a clear snapshot of foreign key dependencies for safe pre-deletion assessments.

Custom solutions for specific needs

For custom database requirements, you might need to modify these scripts:

• Filtering by Schema: Additional WHERE clauses can help filter out results specific to a single schema.
• Parsing Dependency Trees: Generate a dependency tree by recursively joining the sys.foreign_keys view.
• Integration with GUI Tools: These scripts can be incorporated into graphical tools like SSMS or ApexSQL Search for a visual trace of object dependencies.

Compatibility check with SQL Server versions

Ensure your server version aligns with these methods. For instance, SQL Server 2008 might lack features available in the newer versions, yet the provided scripts are designed to function efficiently across these versions.