No, this is not a new initiative by United States Immigration and Customs Enforcement. I am talking databases.

I recently found myself in a situation where I wanted to find all the tables that referenced a particular table via foreign key relationships. I had heard that relying on the built-in dependency tracking (right-clicking the object in SSMS and choosing View Dependencies) was not always reliable. Indeed, SQL Server MVP Aaron Bertrand (Twitter) wrote a great blog post about ways in which sysdepends, sp_depends, and sys.sql_dependencies can lose touch with reality.

I figured I could find the info I wanted by querying the system catalog views. Here is the script I wrote using AdventureWorks as the sample DB.

USE AdventureWorks

GO

DECLARE 

      @ReferencedSchema varchar(1000)

    , @ReferencedObject varchar(1000)

SET @ReferencedSchema = 'HumanResources'

SET @ReferencedObject = 'Employee'

SELECT 

      rs.name + '.' + ro.name AS ReferencedObject

    , rc.name AS ReferencedColumn

    , ps.name + '.' + po.name AS ParentObject

    , pc.name AS ParentColumn

    , co.name AS ConstraintName

FROM sys.foreign_key_columns fkc

INNER JOIN sys.objects co

ON co.object_id = fkc.constraint_object_id

INNER JOIN sys.objects ro

ON ro.object_id = fkc.referenced_object_id

INNER JOIN sys.schemas rs

ON rs.schema_id = ro.schema_id

INNER JOIN sys.columns rc

ON rc.object_id = ro.object_id

AND rc.column_id = fkc.referenced_column_id

INNER JOIN sys.objects po

ON po.object_id = fkc.parent_object_id

INNER JOIN sys.schemas ps

ON ps.schema_id = po.schema_id

INNER JOIN sys.columns pc

ON pc.object_id = po.object_id

AND pc.column_id = fkc.parent_column_id

WHERE rs.name = @ReferencedSchema

AND ro.name = @ReferencedObject

ORDER BY 

      ps.name

    , po.name

Here are the results I get.

You can see that there are 7 foreign key constraints that reference the HumanResources.Employee table in my copy of Adventureworks. You can see that Parent object to which each of these constraints belong. And you can see the actual name of each constraint.

Feel free to take that query and tweak it to add information you would find useful. Also feel free to post it in comments here. I really want my blog to be a two-way street and would love to encourage debate.

I also encourage you to play around with querying the system catalog views. You can find some really cool stuff in there.