SQL Server - Catalog Queries 2005/2008

List FK (Foreign Key) Constraints

This query will return all the foreign key constraints for a SQL Server 2005 database.

Query

SELECT parent_tbl.name as 'parent_tbl',
       foreign_keys.name,
       parent_col.name as 'foreign_key',
       child_tbl.name  as 'references_tbl',
       child_col.name  as 'references_col'     
FROM sys.foreign_key_columns
join sys.all_objects parent_tbl 
on parent_tbl.object_id = foreign_key_columns.parent_object_id
join sys.all_objects child_tbl  
on  child_tbl.object_id = foreign_key_columns.referenced_object_id
join sys.columns parent_col     
on parent_col.object_id = foreign_key_columns.parent_object_id 
and
parent_col.column_id = foreign_key_columns.parent_column_id
join sys.columns child_col     
on child_col.object_id = foreign_key_columns.referenced_object_id 
and
child_col.column_id = foreign_key_columns.referenced_column_id
join sys.foreign_keys         
on foreign_keys.object_id = foreign_key_columns.constraint_object_id                                 
order by parent_tbl.name

Using this query

You can further row restrict this query to drill down on a specific table or group of tables.


 Collapse All    Expand All
Custom Search

SQL Server Information and Resources