考虑数据库中有三个表,比如人,学生,老师。
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... )
使用 SMO,我能够从学生和教师表导航到人员表。
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
}
我想得到相反的结果,例如使用 C# SMO 将 Person 表的person_id引用为外键的所有表(键)是什么。
附言:请建议或建议使用C#,但不要使用DMV。提前致谢
如果你真的想使用 SMO,你可以为此使用 DependencyWalker 对象。
例:
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;
}