This query will return all the foreign key constraints for a SQL Server 2005 database.
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
You can further row restrict this query to drill down on a specific table or group of tables.
Collapse All 
Expand All