Get list of dependent Tables, SQL Server 2005

Question

lets say I have table1 = 'foo' and 4 other tables fee1, fee2, fee3, fee4

now say the primary key of foo is a foreign key of fee1 and fee2.

Given the name 'foo' how will I get to know that fee1 and fee2 has foriegn key dependencies on foo.

Please help, a psuedo query would be helpful.

However, I know how to figure out given the name foo how to get the foriegn key dependencies of foo alone. using INFORMATION_SCHEMA.USAGE_KEY_COLUMN.

Answer 1

Try this in SQL Server Mgmt Studio:

SELECT  
    fk.name,
    OBJECT_NAME(fk.parent_object_id) 'Child table'
FROM 
    sys.foreign_keys fk
WHERE
    fk.referenced_object_id = OBJECT_ID('YourTableNameHEre')

This will list all the foreign key constraints and what table they're coming from that are referencing your YourTableNameHere table.