Get list of referencing tables in SQL Server using C#, SMO

Question

Consider there are three tables in database, say person, student, teacher.

person (person_id, name, person specific columns...),
student(student_id, foreignKey(person_id), student specific columns... )
teacher(teacher_id, foreignKey(person_id), teacher specific columns... )

using SMO, I am able navigate to person table from both student and teacher table.

   ServerConnection serverConnection = new ServerConnection(conn);
   Server server = new Server(serverConnection);
   Database db = server.Databases[databaseName];
   Table tbl = db.Tables("student");

   foreach (ForeignKey fk in tbl.ForeignKeys)
   { 
       //do something
   }

I want to get the reverse, like what are all the tables (keys) referring Person table's person_id as foreign key, using C# SMO.

P.S: please advice or suggest using C#, but not using DMV. Thanks in Advance

Answer 1

If you really want to use SMO, you can use the DependencyWalker object for this.

Example:

var tbl = db.Tables["person"];
var dw = new DependencyWalker(server);
var tree = dw.DiscoverDependencies(new SqlSmoObject[] {tbl}, DependencyType.Children);
var cur = tree.FirstChild.FirstChild;
while (cur != null)
{
    var table = server.GetSmoObject(cur.Urn) as Table;
    if (table != null && table.ForeignKeys.Cast<ForeignKey>().Any(fk => fk.ReferencedTable == tbl.Name))
    {
        //do something with table.Name
    }
    cur = cur.NextSibling;
}