I'm trying to help a teammate walk object dependencies in SQL Server. I've had *some* success using things like
sys.sql_expression_dependencies
(and other related DMV/DMFs) but it's falling short due to a lot of weird edge cases (cross database references, inconsistent naming conventions, etc.)
I'm looking into using SqlSMO to walk object dependencies, and so far, it's working pretty well; at least for object dependencies. I give it a procedure name, and use a DependencyWalker
to tell me things like views, functions and procedures called therein.
But now I need to also know which columns are referenced in the proc. So instead of just knowing my inputProc
references someTable
, I'd like to know that it references someTable.A
and someTable.B
. So far I haven't been able to find a way to do this using SMO. Anyone know if it can be done?
This is an excerpt of C# code which returns the object references; not sure if and how to modify it to include column-level dependencies as well.
public static void Walk()
{
Server server = new Server(@"localhost\koala");
Database db = server.Databases["test"];
Scripter scr = new Scripter(server);
scr.Options.WithDependencies = true;
scr.Options.NoViewColumns = false;
Urn[] urns = new Urn[] { db.StoredProcedures["xdb"].Urn };
DependencyTree dt = scr.DiscoverDependencies(urns, true);
DependencyWalker dw = new DependencyWalker(server);
DependencyCollection dc = dw.WalkDependencies(dt);
foreach (var item in dc)
{
Console.WriteLine(item.Urn.Value);
}
}
Asked by Xedni
(141 rep)
Jul 23, 2018, 07:54 PM